Quick Macro needed

[pklimchuk]

Hello Can someone help me with a Macro

I need a Macro;

If Y2 = "GGJKK", then what ever the value in W2 mulitipy by 2

but exclude the values that are ".20" ".18" ".16" ".14"

 

[CWatts]

Do you mean something like this?

Sub Test()
    I = [W2].Value
    If I <> 0.2 And I <> 0.18 And I <> 0.16 And I <> 0.14 And [Y2].Value = "GGJKK" Then [W2].Value = I * 2
End Sub

 

[pklimchuk]

Sorry didnt work

 

[AlanAnderson]

Hi,
I'm sure there are more elegant ways of doing it but the following does work.

Sub Test1()
If Sheets(1).Cells(2, 25) = "GGJKK" Then
ans = Sheets(1).Cells(2, 23) * 2
    If Not ans = 0.2 And Not ans = 0.18 And Not ans = 0.16 And Not ans = 0.14 Then
        Sheets(1).Cells(4, 25) = Val(ans)
    End If
End If
End Sub

Good luck,

Alan

 

[Michael M]

OR

Sub Test()
Dim I As Integer
    I = [W2].Value
    If I <> 0.2 And I <> 0.18 And I <> 0.16 And I <> 0.14 And [Y2].Value = "GGJKK" Then [W2].Value = I * 2
End Sub

 

[CWatts]

Michael M: It worked fine for me with out declaring the variable on 2007, are earlier versions picky about having the declaration?

 

[Michael M]

Don't know about picky, but...
I ALWAYS declare variables, just in case, regardless of version

Chip Pearson covers it pretty well here !!

http://www.cpearson.com/excel/DeclaringVariables.aspx

 

[pklimchuk]

How do i get it to go all the way donwn for the entire Spreadsheet

not just one sell

Sub Test()
I = [W2].Value
If I <> 0.2 And I <> 0.18 And I <> 0.16 And I <> 0.14 And [Y2].Value = "GGJYKK" Then [W2].Value = I * 2
End Sub

 

[Michael M]

Try this, untested

Sub Test()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "W").End(xlUp).Row
    For r = lr To 2 Step -1
        If r <> 0.2 And r <> 0.18 And r <> 0.16 And r <> 0.14 And Range("Y" & r).Value = "GGJYKK" Then Range("W" & r).Value = I * 2
        Next r
End Sub

 

[pklimchuk]

Try this, untested

Sub Test()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "W").End(xlUp).Row
    For r = lr To 2 Step -1
        If r <> 0.2 And r <> 0.18 And r <> 0.16 And r <> 0.14 And Range("Y" & r).Value = "GGJYKK" Then Range("W" & r).Value = I * 2
        Next r
End Sub

Hi, It didnt work

It changes all the values in W to 0 after multiplying