# Do Wend Macro not working

Hi Guys, I have a simple If Statement within a Do Wend Loop which is not working on Sheet "Data". Anyone any idea why?
Behavioural-Mismatch-Report---Copy.xlsm
ProgrammingMicrosoft OfficeMicrosoft ExcelVBA

Last Comment
[ fanpages ]

8/22/2022 - Mon
Martin Liss

Justin

Martin Liss

Please tell me what you mean when you say it's not working.
Justin

It should change the value in Column A on the same line the 2 IF statements are satisfied and it does not.
Shums Faruk

Try changing this line:
If Cells(i, 4).Value = "4131581" & Cells(i, 13).Value = "Deposit" Then
to
If Cells(i, 4).Value = "4131581" And Cells(i, 13).Value = "Deposit" Then
[ fanpages ]

Hi,

I think we are discussing this routine:

``````Sub Activate()

Worksheets("Data").Select

Dim i As Integer

i = 1

While Cells(i, 1).Value <> ""

If Cells(i, 4).Value = "4131581" & Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"

i = i + 1
Else
i = i + 1

End If

Wend

End Sub
``````

Did you intend this statement:

If Cells(i, 4).Value = "4131581" & Cells(i, 13).Value = "Deposit" Then...

If Cells(i, 4).Value = "4131581" And Cells(i, 13).Value = "Deposit" Then...

?

PS. I would also advise changing:

Dim i As Integer

Dim i As Long
Justin

Hi, it works good. Can you also tell me how if an IF statement in column 4 is satisfied, I can multiply the value in column 11 by -1? This is the code I think

Sub Activate()

Worksheets("Data").Select

Dim i As Long

i = 1

While Cells(i, 1).Value <> ""
ElseIf Cells(i, 4).Value = "4513973" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
Else
i = i + 1

End If

Wend

End Sub
[ fanpages ]

I think what you are trying to do is this:

``````  While Cells(i, 1).Value <> ""

If Cells(i, 4).Value = "4513973" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
Else
i = i + 1
End If

Wend
``````

Are you confident that every value in column [K] is going to be numeric?
Justin

Hi, it works now. However, I am trying to use a Macro button to kick it off, but when I assign the Macro button, It cannot see
the Macro in Module 2. Any ideas? Here's the code that works.

Sub Activate()

Worksheets("Data").Select

Dim i As Long

i = 1

While Cells(i, 1).Value <> ""

If Cells(i, 4).Value = "4131581" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"
i = i + 1

ElseIf Cells(i, 4).Value = "4378014" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"

i = i + 1

ElseIf Cells(i, 4).Value = "4193174" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"
i = i + 1

ElseIf Cells(i, 4).Value = "4193014" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"
i = i + 1

ElseIf Cells(i, 4).Value = "4513973" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
ElseIf Cells(i, 4).Value = "4494550" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
ElseIf Cells(i, 4).Value = "4510417" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
Else
i = i + 1

End If

Wend

End Sub
Shums Faruk

Are you using Shape to call the macro or Command Button?
Shums Faruk

If you are using Shape then you would be able to assign macro on right click, like below image:
If you are using Command Button then right click and select view code, it will take you to blank module, in between Private Sub CommandButton_Click and End Sub, paste your code without Sub & End Sub like below;
``````Private Sub CommandButton1_Click()
Worksheets("Data").Select

Dim i As Long

i = 1

While Cells(i, 1).Value <> ""

If Cells(i, 4).Value = "4131581" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"
i = i + 1

ElseIf Cells(i, 4).Value = "4378014" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"

i = i + 1

ElseIf Cells(i, 4).Value = "4193174" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"
i = i + 1

ElseIf Cells(i, 4).Value = "4193014" And Cells(i, 13).Value = "Deposit" Then
Cells(i, 1).Value = "L14c"
i = i + 1

ElseIf Cells(i, 4).Value = "4513973" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
ElseIf Cells(i, 4).Value = "4494550" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
ElseIf Cells(i, 4).Value = "4510417" Then
Cells(i, 11).Value = Cells(i, 11) * -1
i = i + 1
Else
i = i + 1

End If

Wend
End Sub
``````
Justin

From the developer tab, I am using Insert -> Form Controls -> Button (Form Control), but I cannot see it when I try to assign the Macro.
Shums Faruk

Form Control will not have option of assigning the macro, try using ActiveX Command Button
Justin

Here's the file. Why can I not assign the Macro called "Activate" in Module 2?
Behavioural-Mismatch-Report---21-03.xlsm
Shums Faruk

Justin

the password is "accounts". How come 1 macro is showing but not the other?
Shums Faruk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Justin

superb as ever
Justin

Hi, can you double check your command button? It does not seem to be working.
[ fanpages ]

You're welcome.
Shums Faruk

JCutcliffe,

It is working here, but you should re-open the question, as Fanpages also deserve points.
[ fanpages ]

Thanks Shums.

Your acknowledgement of my contribution was enough.