Inserting formula in array of cells using VBA

[shinobi]

Hi - I’m trying to write some VBA code that puts a formula in a range of cells (a grid of, maybe, 50 cells - which will vary in size, so I first defined the range in my code and gave it a name (say, TargetRange).

I then tried to select that range by using

Range (“TargetRange”).Select

but the code keeps crashing here - doesn’t seem to like the use of the TargetRange variable.

Am I doing that right - or any other ideas?

Thanks!

 

[Saurabhj]

Hi,
What's about below code ? Will it solve the purpose ?

Sub rangeTarget()
    Sheets("Sheet1").Range("A1:C10").Formula = "=$D1*10"
End Sub

 

[shinobi]

Hi,
What's about below code ? Will it solve the purpose ?

Sub rangeTarget()
    Sheets("Sheet1").Range("A1:C10").Formula = "=$D1*10"
End Sub

Hi, thanks for reply. That syntax definitely works. The problem is that, because the Range is variable, I need to use a variable in the brackets (rather than specify “A1:C10”).

I have a variable ‘A1’ and I have a variable ‘c10’. I also have a variable ‘A1:C10’. I just can’t seem to use any of them in the Range function. Is it possible to do that? Perhaps I’m writing it the wrong way? Or maybe the way I’m defining the variables (in the VBA code) is incorrect?

Thanks

 

[Fluff]

Try

Range (TargetRange).Select

failing that please post all the code, so we don't have to guess.

 

[shinobi]

Try

Range (TargetRange).Select

failing that please post all the code, so we don't have to guess.

This works, thanks. The mistake I was making was that I did not define the TargetRange variable with a '.Address' at the end - so when I referenced the TargetRange variable, it did not recognise it as an address.

Thanks again

 

[Fluff]

Glad so sorted it & thanks for the feedback.

 

[smozgur]

This works, thanks. The mistake I was making was that I did not define the TargetRange variable with a '.Address' at the end - so when I referenced the TargetRange variable, it did not recognise it as an address.

I didn't understand what actually works and why it doesn't work in the first place.

If you defined a range as you said and like below:

Then the following, your initial code, must work:

Range (“TargetRange”).Select

... unless you are running the code while the active sheet is not the one that contains this range. In that case, to make sure the parent worksheet to be selected first, then the referenced cell, then you need to use the following instead.

Application.Goto "TargetRange"

On the other hand, if you have TargetRange as an object variable in VBA, which is not accurate with the initial question, then you don't need to use Range.Select but directly TargetRange.Select or Application.Goto TargetRange.

And @Fluff's solution assumes (due to lack of information in the question) the TargetRange = "TargetRange" variable assignment. This is a completely different case than the other two assumptions above. However, you said, "This works".

Range (TargetRange).Select

I am just trying to find the actual solution post in this question to make sure the correct post is marked as the solution. Can you explain your solution with the entire failing code that @Fluff also requested, so I can see how you defined your variables and solved the mystery? Then I can then mark the right solution in the question?

Thanks.

 

[shinobi]

Thanks for your email. Appreciate it's helpful to document the correct problem and solution. I'll try and elaborate.

1) I defined a range of cells in my VBA code, not in the worksheet itself - I called this range TargetRange, and I defined it as follows, referencing a pivottable on the worksheet:
TargetRange = PivotRange.Cells(PivotRange.Rows.Count, PivotRange.Columns.Count).Offset(-1, 0)

2) I then tried to use the following syntax to select that range of cells:
Range("TargetRange").Select or even Range(TargetRange).Select

3) In fact, neither of the options in 2 above worked, because what I actually needed to do was add ".Address" to my definition of TargetRange, as follows:
TargetRange = PivotRange.Cells(PivotRange.Rows.Count, PivotRange.Columns.Count).Offset(-1, 0).Address

Now, using Range(TargetRange).Select, I was able to select the range of cells

So the actual error was failing to properly define the range - which made referencing it fail. I guess that you are saying that an alternative would be to also use just TargetRange.Select, which I think would also work. I'm not so strong on VBA these days (having dabbled with it a little bit about 15 years ago!) - so appreciate my initial framing of the problem was likely lacking in details.

Thanks

 

[smozgur]

@shinobi - Let me explain the issue in your approach.

1. Cells.Offset() method returns a range object. Therefore, you need to use Set statement to create an object variable if you want to use this approach as shown below:
   

   ' Set the range object variable
   Set TargetRange = PivotRange.Cells(PivotRange.Rows.Count, PivotRange.Columns.Count).Offset(-1, 0)
   ' Now you have the range object, you can use the Select method directly
   TargetRange.Select

2. Range("TargetRange").Select is the approach to select a named range in the worksheet (or by using the range address as you did). So, if you have a range named "TargetRange" on the worksheet, then you can use this method.
3. Cell.Address method returns the address of the given range as a string value. Therefore, you see it is working with #2 above.
   

   ' TargetRange is a string variable since the Address method returns the range address as a string value.
   TargetRange = PivotRange.Cells(PivotRange.Rows.Count, PivotRange.Columns.Count).Offset(-1, 0).Address
   ' Now we have the address string that we can use with Range property.
   Range(TargetRange).Select

As you can see, all these methods work if they are used correctly. It looks like what you were missing is setting an object variable that explained at #1, or if you meant to store the range address in the TargetRange string variable, then the Address method as you also found out. It was very easy to catch what was wrong if you initially provided the TargetRange assignment in the original question. Please try to provide more information next time as @Fluff also suggested, then you'll surely get a faster result.

I am marking your previous post as the solution to this question. Thanks for the explanation that would help future readers.

 

[shinobi]

Thanks for taking the time to provide this follow up - always good to learn something through the process!