0

i am having trouble with my do until function in my VBA. For some reason it does not go into the next row and doesnt stop when the column cell is empty. please any help will do. 

Sub rollforward()

Dim myfile As String
Dim myfolder As String

myfolder = "\\tps-san\Share\ Accounting\Inventory\2019 Inv\Inventory Rollforward\11 Nov"

myfile = Dir(myfolder & "\*.xlsx")

Do While myfile <> ""
Workbooks.Open Filename:=myfolder & "\" & myfile
myfile = Dir
Loop

Workbooks("Beg Balance").Activate

Dim i As Integer
i = 17

Do Until IsEmpty(ActiveCell)

If Cells(i, 2) = "0011" Or "0021" Or "0705" Or "20" Or "21" Then Cells(i, 2) = "0020"
If Cells(i, 2) = "9999" Then Cells(i, 2) = "9011"
If Cells(i, 2) = "9650" Or "9599" Or "9972" Or "9940" Then Cells(i, 2) = "9031"
If Cells(i, 2) = "9230" Or "9059" Then Cells(i, 2) = "9059"
If Cells(i, 2) = "4212" Or "7212" Then Cells(i, 2) = "9212"
If Cells(i, 2) = "9214" Then Cells(i, 2) = "9214"
If Cells(i, 2) = "9408" Then Cells(i, 2) = "9415"
If Cells(i, 2) = "9916" Then Cells(i, 2) = "9500"
If Cells(i, 2) = "9215" Then Cells(i, 2) = "9535"
If Cells(i, 2) = "9055" Then Cells(i, 2) = "9737"
If Cells(i, 2) = "9706" Or "2037" Then Cells(i, 2) = "9770"
If Cells(i, 2) = "7901" Or "7903" Then Cells(i, 2) = "9905"
If Cells(i, 2) = "9262" Then Cells(i, 2) = "9915"

Loop
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
joe dauda
  • 1
  • You are not adding `1` to `i` in each loop, nor are you activating another cell to test so it is testing the same one. Instead find the last cell with a value then use a for loop. – Scott Craner Dec 20 '19 at 16:34
  • Don't rely on `ActiveCell`. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. – BigBen Dec 20 '19 at 16:34
  • so at the end i should add i = i + 1 @ScottCraner – joe dauda Dec 20 '19 at 16:35
  • 1
    No there is more, I would recommend finding the end before the loop and using a For Loop instead. – Scott Craner Dec 20 '19 at 16:45
  • `If Cells(i, 2) = "0011" Or "0021" Or "0705"` etc... Doesn't do what you think it does. – braX Dec 20 '19 at 17:19
  • @braX could you explain to me what i need so it does do it? – joe dauda Jan 02 '20 at 14:27
  • `If Cells(i, 2) = "0011" Or Cells(i, 2) = "0021"` etc : each item between the `Or` has to be a complete comparison. – braX Jan 02 '20 at 16:43

1 Answers1

0

Instead of using active cell, try to find the last value first then assign it to a variable and use a for loop. This should work for you.

Just change your sheet and the column you are looking at. The last row is found using the same way is if you pressed Ctrl + Shift + End in the column you are looking at.

Dim ws As Worksheet
Dim lastRow As Integer, i As Integer

Set ws = ThisWorkbook.Sheets(1)

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow

    'Do loop things here

Next
tomBob
  • 128
  • 6