0

I have a small issue here. The I want to make only one cell reference edit in this macro, by changing the reference for rcell9-- in this case it is "F2". And I also want to avoid using .Activate. However I want rcell5 to be the value in the cell that rcell9 references. How can I program this differently?

 Sub Noname()
 Dim rcell5 As String, rcell9 As String
 Dim MonthDays As Long
 rcell9 = ("F2")
 Range(rcell9).Activate
 rcell5 = ActiveCell.Value
 End Sub
Kisheon
  • 43
  • 7

1 Answers1

1

I suggest a few small tweaks:

Sub NoName2()
Dim rCell5 As String, rCell9 As Range
' Dim monthDays As Long ' Why have this?

Set rCell9 = Range("F2")
rCell5 = rCell9.Value

End Sub

While the above should work for you, I've found myself thinking - what's your goal with the sub? The variable names aren't really helpful, and IMO a little confusing. You want a string to get its value from a cell, that's normal. However the naming convention makes the code a little confusing to read, even when it's so short.

I suspect there's a bigger plan here, so just consider what that may be, and I suggest tweaking the variable names to be more in tune with that.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Thanks. Yes. It is a part of a bigger code, but that's the last step I was missing. I just wanted to not have to put that cell reference in twice. I can share the entire code with you if you really want to see it. It is not absolutely efficient, but a lot better than I had before. – Kisheon Dec 29 '16 at 15:30
  • @Kisheon - Did this work for you? If so, mind marking as the answer? Also, I recommend looking at [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) which will help. – BruceWayne Dec 29 '16 at 16:10
  • The first comment from @Cominterm worked without having to adjust my code, because I need rcell9 as a string. I have cut out a few .Select and .Activate from my code, but I was unable to get around a few others. – Kisheon Dec 29 '16 at 16:48