Can someone tell me why this code won't run

[Woody75]

Hi,

Complete novice here again... I was wondering if anyone could tell me why this code wont run or doesn't do anything

Sub Button3_Click()


Dim Counter2 As Integer
Counter2 = 2

Do While ThisWorkbook.Sheets("Reports Input").Cells(Counter2, 5).Value = "END"

If ThisWorkbook.Sheets("Reports Input").Range("E" & Counter2).Value <> "" Then
ThisWorkbook.Sheets("Reports Input").Range("A" & Counter2).Value = ThisWorkbook.Sheets("Reports Input").Range("G" & Counter2).Value
ThisWorkbook.Sheets("Reports Input").Range("E" & Counter2).Value = ThisWorkbook.Sheets("Reports Input").Range("B" & Counter2).Value
ThisWorkbook.Sheets("Reports Input").Range("F" & Counter2).Value = ThisWorkbook.Sheets("Reports Input").Range("C" & Counter2).Value
Else
MsgBox "test"
End If

Counter2 = Counter2 + 1

Loop

End Sub

Many Thanks

Woody

 

[Jonmo1]

The first thing that comes to mind is that VBA can be very case sensitive..
END vs End vs end..

try

Do While Ucase(ThisWorkbook.Sheets("Reports Input").Cells(Counter2, 5).Value) = "END"

 

[JoeMo]

Does it not run or, it runs but doesn't appear to do anything? If the latter, when you run it, does Sheet "Reports Input" cell E2 have the value "END" in it with no extraneous spaces or other characters?

 

[Jonmo1]

Does it not run or, it runs but doesn't appear to do anything? If the latter, when you run it, does Sheet "Reports Input" cell E2 have the value "END" in it with no extraneous spaces or other characters?

Good point..

Perhaps you want to use
Do Until ..... insetad of Do While..

 

[Woody75]

No, that doesn't work either

 

[Woody75]

Does it not run or, it runs but doesn't appear to do anything? If the latter, when you run it, does Sheet "Reports Input" cell E2 have the value "END" in it with no extraneous spaces or other characters?

No Cells E2:E65 are all blank, the cell with end in is at E66

 

[Woody75]

Good point..

Perhaps you want to use
Do Until ..... insetad of Do While..

Do Until works like a charm, many thanks

 

[Woody75]

OK, now that i've sorted that out, I've expanded my code to include a 2nd Do Until Loop to find a value in cell ("E" & Counter3) in a separate ws and replace it with the value of cell ("G" & Counter2). The code runs fine but the value of the cell in the sep ws does not update. Is this because that range of cell are validated (Drop Down Menu) and if so how do i get around it?

Cheers

Woody

 

[Woody75]

sorry forgot to post the code

Sub Button3_Click()


Dim Counter2, Counter3 As Integer
Counter2 = 2
Counter3 = 2


Do Until ThisWorkbook.Sheets("Reports Input").Cells(Counter2, 5).Value = "end"

If ThisWorkbook.Sheets("Reports Input").Range("E" & Counter2).Value <> "" Then
ThisWorkbook.Sheets("Reports Input").Range("G" & Counter2).Value = ThisWorkbook.Sheets("Reports Input").Range("A" & Counter2).Value
ThisWorkbook.Sheets("Reports Input").Range("B" & Counter2).Value = ThisWorkbook.Sheets("Reports Input").Range("E" & Counter2).Value
ThisWorkbook.Sheets("Reports Input").Range("C" & Counter2).Value = ThisWorkbook.Sheets("Reports Input").Range("F" & Counter2).Value

Do Until ThisWorkbook.Sheets("Obs Sheet").Cells(Counter3, 5).Value = ""

If ThisWorkbook.Sheets("Obs Sheet").Range("E" & Counter3).Value = ThisWorkbook.Sheets("Reports Input").Range("G" & Counter2).Value Then

ThisWorkbook.Sheets("Obs Sheet").Range("E" & Counter3).Value = ThisWorkbook.Sheets("Reports Input").Range("G" & Counter2).Value

End If

Counter3 = Counter3 + 1

Loop

ThisWorkbook.Sheets("Reports Input").Range("E" & Counter2, "G" & Counter2).Value = ""

End If

Counter2 = Counter2 + 1

Loop

End Sub

 

[Woody75]

Never mind, I think I have it....

Thanks All