Solved How do I use variables to define a set Range in Excel?

January 13, 2012 at 10:28:48
Specs: Windows XP

Hello,

I'm having difficulties in attempting to define a range with variables for both the starting row and ending row.

I currently have the following defined:

Sheets("ExistingData").Select
LastWorked = ActiveSheet.UsedRange.Rows.Count

Sheets("NewData").Select
LastRow = ActiveSheet.UsedRange.Rows.Count

Which sets my variables for the row values for the starting and ending..

I am trying to use the following selection value to copy and paste my formula into the cells but is failing.

Selection.AutoFill Destination:=Range("A" & LastWorked & ":A" & LastRow), Type:=xlFillDefault

Is the Range values not valid? It keeps returning an error 1004.

If I set it to Selection.AutoFill Destination:=Range("A3:A" & LastRow), Type:=xlFillDefault, it works fine but I need the first range value to be dynamic as well as I'm trying to have it update my existingdata spreadsheet with any new values that come in on newdata which is a different sheet I copy and paste over daily and want it to continue from the end of the existingdata instead of always from the beginning.

Any help would be appreciated.

Thanks!


See More: How do I use variables to define a set Range in Excel?

Report •


#1
January 13, 2012 at 10:55:40

A couple of things could be going on.

First, while this won't solve your problem, you should be aware that rarely do you have to Select an object in VBA to perform an action on it. In fact, selecting objects is very inefficient.

Instead of this:

Sheets("ExistingData").Select
  LastWorked = ActiveSheet.UsedRange.Rows.Count

You could use this:

LastWorked = Sheets("ExistingData").UsedRange.Rows.Count

However, using the UsedRange property to determine the last row with data can be problematic as I explained in Response #1 of this thread:

http://www.computing.net/answers/of...

A better method might be:

LastWorked = Range("A" & Rows.Count).End(xlUp).Row

That said, have you checked to see that LastWorked actually equals 3 when you use your UsedRange method? If LastWorked is a value that VBA can't use to build the Range Address then it will throw up an error.

See my How-To for some VBA troubleshooting tips, including how to check to see what a variable is being set to while the code is running:

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 13, 2012 at 11:31:13

Thanks DerbyDad03!

I took your advised and Adjusted LastWorked to be LastWorked = Range("A" & Rows.Count).End(xlUp).Row as it was more accurate. For some reason the UsedRange value defaulted to match it for LastRow as well after the initial run whereas your suggestion retained the correct value.

I took a look at the debug, and it's stalling at this line:

Selection.AutoFill Destination:=Range("A" & LastWorked & ":A" & LastRow), Type:=xlFillDefault

With the comments:

Run-time error '1004':

AutoFill method of Range class failed.

I took a look at your troubleshooting tips and the defined values of LastWorked and LastRow are valid.

Ah.. I see now, it looks like it's because of this perhaps?

ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK('NewData'!R[0]C[0]),"""",'NewData'!R[0]C[0])"

Basically, what I'm trying to do with the above formula is copy a specific column of data from NewData and populate it into the ExistingData worksheet.

In order to do this, I previously had this:

Sheets("ExistingData").Select
Range("A3").Select

ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK('Flash Report'!R[0]C[0]),"""",'Flash Report'!R[0]C[0])"


Which selected the existing Data spreadsheet, selected the first cell, A3, and then dumped the formula in and copied it using:

Selection.AutoFill Destination:=Range("A3:A" & LastRow), Type:=xlFillDefault

Is it an issue with how inserting the formula?

Sorry if I'm jumping all over the place with it.. trying to be as concise as possible.


Report •

#3
January 13, 2012 at 12:53:48
✔ Best Answer

It's hard for me tell exactly what's going on since I can't see your spreadsheet and I'm only seeing snippets of code.

Have you considered this syntax? It worked for me in a blank workbook with Sheets named to match yours.

All "selections" have been eliminated.

Sub FormulaFill()
'Hard coded values for testing purposes only...
  LastWorked = 3
  LastRow = 25
'Fill with formula
  Sheets("Existing Data").Range("A" & LastWorked & ":A" & LastRow).FormulaR1C1 = _
   "=IF(ISBLANK('Flash Report'!R[0]C[0]),"""",'Flash Report'!R[0]C[0])"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 13, 2012 at 13:50:47

Looks like it worked! Thanks alot! I'm always a bit stumped as my VB scripting kinda sucks.. :( I appreciate your assistance with it and apologize for the snippets. There wasn't much else there =/

Report •


Ask Question