For each cell in range problem

[thechazm]

Hello All,

First time posting here so hello :D

I am having a problem with scrolling through each object in a for each loop. Code:

Function detectDateRange(xlsRemoteApp As Excel.Application, xlsRemoteWB As Excel.Workbook, xlsRemoteSheet As Excel.Worksheet, strSheetName As String)
Dim LastAddress As String
Dim xlsSheet As Excel.Worksheet, xlsApp As Excel.Application, xlsWB As Excel.Workbook
Set xlsApp = Application
Set xlsWB = xlsApp.Workbooks(1)
Set xlsSheet = xlsWB.Worksheets(strSheetName)
LastAddress = xlsRemoteSheet.Range("A1:A120").End(xlToRight).Address
For Each r In xlsRemoteSheet.Range("$A$3:" & LastAddress)
    Debug.Print r.Address
    xlsSheet.Range(r.Address).Formula = LinkCell(xlsRemoteApp.CommandBars("Web").Controls("Address:").Text, xlsRemoteSheet.Name, r.Address)
Next r
Set xlsSheet = Nothing
Set xlsWB = Nothing
Set xlsApp = Nothing
End Function

The above code runs without any errors but the r.Address seems to be giving me the wrong cell location?

So the purpose is to scroll through each cell in the remote sheets range from "A3" to whatever the last cell that has data up until cell "A120". The problem is here is the output from my debug print:

$A$1
$B$1

Even though I am passing it "For Each r In xlsRemoteSheet.Range("$A$3:" & LastAddress)" you can see that it starts at A1 and then goes to B1? Can someone maybe point out a clue of what I am missing here? Also just as a note LastAddress was returning "$AL$1" so it's supposed to go through the range ("$A$3:$AL$1").

Thanks for the help,

TheChazm

 

[Firefly2012]

Where do you think your last cell is?

 

[Jonmo1]

Welcome to the board..

Perhaps you should change
LastAddress = xlsRemoteSheet.Range("A1:A120").End(xlToRight).Address
to
LastAddress = xlsRemoteSheet.Range("A1").End(xlDown).Address

 

[thechazm]

The problem was just me being a dummy... Please forgive me for wasting everyone's time. The reason why it was not working is because I mixed up my columns and rows.... I was trying to go left to right but instead I had it looking down :/ Not cool... Thanks anyway.