Solved VBA loop issue

[CosmaL]

Dear friends,

I have 2 workbooks.
1st contains the data and the 2nd contains specific cells from 1st workbook.

Code is runing under a button in the 1st book.

It's working for each raw manually, if i don't use the loop.

Using the loop it doesn't copy anything.

Any ideas?

Sub UpdateActionPlan()

Dim intAdded As Integer, intSRow, intTRow
Dim sngRecord(6)
Dim strTFullPath As String
Dim cancel As Integer
Dim strFileName As String
Dim i As Integer
Dim LastRow As Integer
Dim FirstRow As Integer

FirstRow = 7
LastRow = 1000
i = FirstRow


strFileName = “Test.xls”
strTFullPath = strFileName
Workbooks.Open filename:=strTFullPath

Do Until i > LastRow
If Range("S" & i).Value = "Action" Then
sngRecord(0) = Range("C" & i).Value
sngRecord(1) = Range("G" & i).Value
sngRecord(2) = Range("N" & i).Value
sngRecord(3) = Range("O" & i).Value
sngRecord(4) = Range("P" & i).Value
sngRecord(5) = Range("T6").Value

With Workbooks("Test.xlsx").Worksheets("CheckSheet")
intTRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Range("C" & intTRow) = sngRecord(0)
.Range("E" & intTRow) = sngRecord(1)
.Range("G" & intTRow) = sngRecord(2)
.Range("A" & intTRow) = sngRecord(3)
.Range("D" & intTRow) = sngRecord(4)
.Range("B" & intTRow) = sngRecord(5)
End With
Else
Cancel = True
End If

i = i + 1
Loop

End Sub

 

[Gasman]

Wlak through your code line by line and inspect the variables.
Might want to use code tags as well as I hope it was initially indented?

 

[Isaac]

If you upload a sample workbook that I can run this code on I'll look at it for you and debug it.

One big problem is I see an unqualified range which could belong to any worksheet and any workbook which is not good you always have to fully qualify everything either by writing out the full qualification or having previously assigned it to a variable etc

 

[silentwolf]

Hi,

strFileName = “Test.xls”
strTFullPath = strFileName
Workbooks.Open filename:=strTFullPath

strTFullPath = " Test.xls"

there is no full filepath.

 

[Isaac]

Hi,

strFileName = “Test.xls”
strTFullPath = strFileName
Workbooks.Open filename:=strTFullPath

strTFullPath = " Test.xls"

there is no full filepath.

Good eye although I wonder why that wouldn't produce a runtime error

 

[Gasman]

Good eye although I wonder why that wouldn't produce a runtime error

Well it is meant to have worked without the loop?

 

[Isaac]

I think there is some vague concept about the current directory wherein possibly you could omit a file name in all kinds of programming but I've of course never ventured there because it's so vague and I wouldn't care to

 

[CosmaL]

@Isaac, silentwolf If i remove the loop and use the cursor line it will copy the data.​

@Gasman with break and line by line it's working fine!​

 

[Gasman]

Well you open test.slsx ? so that is the active workbook, and nothing in it?

Try selecting the Data workbook after opening that file?

ALso Introw starts at 2, and never gets incremented?

 

[cheekybuddha]

In the code in Post#1 you open test.xls

Later in your With block you reference test.xlsx

Which is it?

 

[Gasman]

I amended the top range to 10 just for testings.

 

[Gasman]

In the code in Post#1 you open test.xls

Later in your With block you reference test.xlsx

Which is it?

O/P supplied Test.xlsx in the zip file so I amended it to that?

 

[CosmaL]

Well you open test.slsx ? so that is the active workbook, and nothing in it?

Try selecting the Data workbook after opening that file?

ALso Introw starts at 2, and never gets incremented?

introw gets incremented everytime at the end of the loop.
If test.xlsx is closed, code does open it but it doesn't retrieve any data!

 

[cheekybuddha]

Where is the code run from?

An excel workbook or access?

(Am viewing on a phone and can't look at attachment)

 

[Gasman]

@cheekybuddha
Code is in a xlsm

 

[CosmaL]

In the code in Post#1 you open test.xls

Later in your With block you reference test.xlsx

Which is it?

I open it and then with block i copy the data from data.xlsm

 

[Gasman]

introw gets incremented everytime at the end of the loop.

Really?

It stays the same number 2 for me and just overwrites the values?

Here is a run for up to 20.
You compare the data.

 

[Gasman]

Debug.print shows

Inttrow is 2 and i is 8
Inttrow is 2 and i is 11
Inttrow is 2 and i is 18

You really need to test your code.

 

[CosmaL]

Debug.print shows

Inttrow is 2 and i is 8
Inttrow is 2 and i is 11
Inttrow is 2 and i is 18

You really need to test your code.

View attachment 104422

And after 18 should be 21, where S column is "action".
It's really strange that inttrow remains 2, it should start from line 4.

 

[CosmaL]

In the code in Post#1 you open test.xls

Later in your With block you reference test.xlsx

Which is it?

Sorry it's .xlsx, corrected