VBA -- code is skipping past my for/next loops:(

[GregN]

Greetings all... I have a subroutine that opens a 'data file' and moves the data from the current workbook to sheet 1 of the data file and then closes the data file.

I have a similar routine that does the opposite.

The idea is that I have a single file hidden from the users (read our sales manager who doesn't understand computers) that the spreadsheet draws from each time it's opened. Also, I have an 'edit' function where the user can change this data, or add to it, and it will save back to the original data file.

The first bit of code works great;

Sub GetCustData()
    
    Dim Target_Workbook As Workbook
    Dim Source_Workbook As Workbook
    Dim Source_Path As String
    Dim numrows As Long
    Dim i As Integer
    Dim j As Integer
    
    'Application.ScreenUpdating = False
    
    Set Target_Workbook = ThisWorkbook
    Source_Path = "B:\Current Jobs\Customers\ForQuotes.xlsx"
    Set Source_Workbook = Workbooks.Open(Source_Path)
    
    numrows = Source_Workbook.Sheets(1).Cells(1, 2)
    
    For i = 1 To 16
        For j = 1 To numrows
            Target_Workbook.Sheets("Lookups").Cells(j + 1, i) = Source_Workbook.Sheets(1).Cells(j + 1, i)
        Next j
    Next i
    
    Source_Workbook.Close False
    
    'Application.ScreenUpdating = True
End Sub

So, I figured that I'd do the reverse when I wanted to save... (note... the msgbox()'s are there to see that the code was working... msgbox("hi") and msgbox("hi2") show up, but msgbox("hi1.5") never shows up...

Any ideas??

Sub UpdateDataSource()
    'this does the opposite of what the code on the button does
    'the target is the this worksheet, the source is data file
    
    Dim Target_Workbook As Workbook
    Dim Source_Workbook As Workbook
    Dim Source_Path As String
    Dim numrows As Long
    Dim i As Integer
    Dim j As Integer
    i = 0
    j = 0
    
    'Application.ScreenUpdating = False
    
    Set Target_Workbook = ThisWorkbook
    Source_Path = "B:\Current Jobs\Customers\ForQuotes.xlsx"
    Set Source_Workbook = Workbooks.Open(Source_Path)
    
    MsgBox ("hi")
    
    numrows = Target_Workbook.Sheets(1).Cells(1, 1)
    
    For i = 1 To 16
        For j = 1 To numrows
            'MsgBox ("I is " & i & "  J is " & j)
            MsgBox ("hi 1.5")
            Source_Workbook.Sheets(1).Cells(j + 1, i) = Target_Workbook.Sheets("Lookups").Cells(j + 1, i)
        Next j
    Next i
    
    MsgBox ("hi 2")
    
    Source_Workbook.Save
    Source_Workbook.Close False
    
    'Application.ScreenUpdating = True

End Sub

 

[Jonmo1]

What is the value of the numrows variable at the time you run the code ?

Perhaps this line isn't looking at the correct sheet
numrows = Target_Workbook.Sheets(1).Cells(1, 1)

So Cells(1,1) is blank, then the loop line
for j = 1 to numrows
is actually reading as
for j = 1 To 0


Are you sure it's Sheets(1) ?
Or should it be Sheets("Sheet1") ?

 

[tonyyy]

In the GetCustData module you have:

 numrows = Source_Workbook.Sheets(1).Cells(1, 2)

In the UpdateDataSource module you have:

numrows = Target_Workbook.Sheets(1).Cells(1, 1)

Could it be that your cell reference in the second module needs to be Cells(1,2)?

Cheers,

tonyyy

 

[GregN]

What is the value of the numrows variable at the time you run the code ?

Perhaps this line isn't looking at the correct sheet
numrows = Target_Workbook.Sheets(1).Cells(1, 1)

So Cells(1,1) is blank, then the loop line
for j = 1 to numrows
is actually reading as
for j = 1 To 0

I think you may be on to something here. I'll drop a msgbox to display the contents of numrows and verify that... it should be reading the correct value -- I have a =counta(a:a) formula sitting in A1 of that sheet (the same formula is at B1 on the source file) but I'll make sure that it's looking there.

Are you sure it's Sheets(1) ?
Or should it be Sheets("Sheet1") ?

Yeah, I'm sure about this. The source file is a default Excel file -- there are three sheets (Sheet1, Sheet2 and Sheet3) so Sheets(1) would be the first sheet and therefore, "Sheet1"... a good place to look.... I'll double check it to make sure, though.

Thanks for the input!!

Greg

 

[GregN]

In the GetCustData module you have:

 numrows = Source_Workbook.Sheets(1).Cells(1, 2)

In the UpdateDataSource module you have:

numrows = Target_Workbook.Sheets(1).Cells(1, 1)

Could it be that your cell reference in the second module needs to be Cells(1,2)?

Cheers,

tonyyy

Excellent point... The reason for the discrepancy is that in the 'source' sheet (the one that I'm keeping as the master), I set that up first. And, when I did that, I put two counts at the top of the sheet. In A1 I put the number 2 to tell where the range starts and in B1 I put =counta(b1:b100000) to show the last row of data. When I went to set up the data in the second, or slave sheet, I realized that I didn't need the 2 in A1 -- it was always going to start there, so I put the counta in A1. And, I'm too lazy to either move it, or change the original

Of course, I'm also the guy who, instead of putting 30 seconds on the microwave timer, I put 33 'cause I'm too lazy to bring my finger down to the 0 after hitting 3

I should probably fix this as it's going to cause issues later, I'm sure.

Greg

 

[GregN]

Problem solved... it turns out that I was mixed up as to what workbook I was looking at... sheet(1) only works on the source file. The working file's 'sheet1' didn't have anything at A1. I changed it to "sheet("Lookups")" (the correct sheet) and it works great!

Thanks guys for the input... sometimes you get too close to the forest to see those trees...

Greg

 

[Jonmo1]

You're welcome.