If statement to sendspecialkeys [field+] or [field-]

[ianjwerner]

I need help with how to write an if statement in VBA so that if the value in a cell is positive it will Call SendSpecialKeys("[field+]") or if the value is a cell is negative it will Call SendSpecialKeys("[field-]")

 

[Micron]

If range("A1") > 0 And range("A1") <> 0 Then
   SendSpecialKeys("[field+]")
ElseIf range("A1") < 0 And range("A1") <> 0
   SendSpecialKeys("[field-]")
End If

>0 is +ve; <0 is negative but 0 is neither so you really have 3 conditions to worry about. What I wrote will do nothing if cell is 0 but I'm thinking it could be simpler:

If range("A1") > 0 Then
   SendSpecialKeys("[field+]")
ElseIf range("A1") < 0
   SendSpecialKeys("[field-]")
End If

This will also do nothing if cell value is 0.

Perhaps you will need to worry about no value in the cell as well.
Provide your own range/cell reference instead of A1. Could also be done with a Select Case block.
BTW, you rarely need to use Call statement.

 

[ianjwerner]

If range("A1") > 0 And range("A1") <> 0 Then
   SendSpecialKeys("[field+]")
ElseIf range("A1") < 0 And range("A1") <> 0
   SendSpecialKeys("[field-]")
End If

>0 is +ve; <0 is negative but 0 is neither so you really have 3 conditions to worry about. What I wrote will do nothing if cell is 0 but I'm thinking it could be simpler:

If range("A1") > 0 Then
   SendSpecialKeys("[field+]")
ElseIf range("A1") < 0
   SendSpecialKeys("[field-]")
End If

This will also do nothing if cell value is 0.

Perhaps you will need to worry about no value in the cell as well.
Provide your own range/cell reference instead of A1. Could also be done with a Select Case block.
BTW, you rarely need to use Call statement.

hello!
when trying to input the VBA code it produces an error "Compile error: Expected: Then or GoTo

 

[Micron]

That is not what I posted. ElseIf and Else If are not the same. "Then" is supposed to be optional with ElseIf, so first try ElseIf as I posted.

 

[ianjwerner]

I receive a syntax error when I paste as is.

 

[Micron]

You've got you code in a standard module. As such, it stands alone and has no idea what "Range" is all about. I wrote it as if your code was on a sheet but it's not, so you have to be specific about the range. I think you'll need to qualify it as in Sheets("yourSheetNameHere").Range("A1") but then, I don't see why it didn't error on the previous If line (or did it?). Documentation about ElseIf says "Then" is optional but you could try adding Then to that line.

I have to go out for a few hours so won't be able to reply until later.

 

[ianjwerner]

It worked when I put the extra "Then" Thank you very much for your help!

 

[Micron]

Sorry about that. I seldom use ElseIf, preferring Select Case instead. I must have mis-read the documentation before posting, because I thought it said Then was optional but I can't find that now. Glad I could help and thanks for the recognition.

 

[Scott Huish]

Then is not optional with ElseIf. From the help file:

You don't need a Then with Else but you do with ElseIf