xlDown won't select lastRow, help selection lastRow w/o using xlUp

[pawest]

Hi there,
I'm pulling data from FTP files and using that data to calculate a monetary balance. The FTP file has some blank cells and the xlUp function gets stuck... it doesn't really cause a problem but I would prefer to select the first cell in the sheet and find the range by using xlDown, rather than selecting the last cell and using ulUp. When I attempt to input the lastRow variable into a cell using the .Formula method, it selects the first row. What's going on with my code here?

Sub BalanceCheck()

Dim lastRow As Integer

lastRow = Range("A1").End(xlDown).Row


Dim CCYbalance As Range
Set CCYbalance = Sheets("Balances").Range("B4")


    CCYbalance.Formula = "=SUMIF('Positions'!G2:G" & lastRow & _
    ",""<>USD""," & "'Positions'!J2:J" & lastRow & ")"

The resulting formula looks like this: =SUMIF('Positions'!G2:G2,"<>USD",'Positions'!J2:J2)" ...obviously not what I want. I want it to select the range in Columns G and J and not just the first cell of the range.

 

[Joe4]

Starting at the top and using xlDown to find the last row of data only works if there are no blanks in your data. If cell A2 has data, but cell A3 is blank, your formula will return row 2 as the last row of data.

Make sure that column A is the column you want to use in determining last row.

 

[pawest]

Joe,
Thanks for your response. I'm sure I'm using the right column. The FTP file I'm referencing will always have data in Column A for the given region and will not have blanks (unless there's an anomaly). More often than not, there are blank cells within the columns and the resulting xlUp technique stops at the blanks. There shouldn't be any values between the real data and the anonymous blank cells... but as a paranoid excel dude it scares me a little bit.

Any other suggestions?

 

[Joe4]

Let's make sure things are working the way you expect. What does this return when you run it?

Sub CheckLastRow()

   Dim lastRow As Integer
   lastRow = Range("A1").End(xlDown).Row
   MsgBox lastRow

End Sub

 

[pawest]

OK, I figured out why it wasn't working from the beginning... but this presents a new issue.

I have many sheets in this workbook. Rather than specifying lastRow as

= Range("A1").End(xlDown).Row

I had to change it to:

=Sheets("Positions").Range("A1").End(xlDown).Row

The reason why this doesn't help me for my project is I want lastRow to be dynamic and I want to specify the sheet lastRow references in the .formula = "SUMIF(" technique.
Do you have further suggestions with making lastRow dynamic?
</pre>

 

[EdNerd]

Don't you have a variable set to your worksheet?
Can't you use that to specify the range?
myWks.Range("A1").End(xlDown).Row ?

Ed

 

[pawest]

Ed,
I tried doing this and it's giving me an error. Any further suggestions would be helpful:

Sub BalanceCheck()

Dim wSheet As Worksheet
Dim lastRow As Integer
Dim CCYbalance As Range 

wSheet = ActiveSheet
lastRow = wSheet.Range("A1").End(xlDown).Row

Set CCYbalance = Sheets("Balances").Range("B4")       

Sheets("Positions").Activate 'I would then activate the necessary worksheet when each calculation is being performed
CCYbalance.Formula = "=SUMIF('Positions'!G2:G" & lastRow & _     ",""<>USD""," & "'Positions'!J2:J" & lastRow & ")"