Simple Excel and VB code help please....

[AlexJames]

Hey guys... for all you excel gurus out there...

 

I need some simple code but i seem to remember some of the code is written slightly different when using vb in excel instead of vb stand alone.

 

I need something simple like ...

 

If c2.value = "***" then msgbox ("correct")

 

or

 

If c2.value = "***" then D11.value = "something else"

 

I know that in excel If statments will work when I code them against a command button but the .value field is obviously not correct... am I making sense?

[stevie_b]

I assume c2 is a command button, is it? If so, what do you intend (in real world terms) the "value" to be? The only thing that command buttons usually do is kick off some code when they're clicked.

[AlexJames]

no c2 would be the excel cell ref.

[stevie_b]

Something like this:

 

Sub wobble()

If Range("C2").Value = "yes" Then

MsgBox "hello"

ElseIf Range("C2").Value = "no" Then

MsgBox "goodbye"

Else

MsgBox "pepper-crusted fish in a lime and corriander vinigrette"

End If

 

End Sub

[Jake]

If Range (“C2”) = “blah” Then Msgbox “Correct”

[stevie_b]

As Jake said, you don't need to specify the ".Value" bit: Excel assumes that's what you mean.

[AlexJames]

ahh thanks guys! your legends.. I'll give that a bash and let u know how I do!

[stevie_b]

When you try it, you'll need to insert the code into the module for the sheet you want to operate on. If you insert it into a generic module not attached to a sheet, Excel doesn't know which sheet you're talking about: they each will contain a cell C2.

[AlexJames]

true... i never thought of that... so if I wanted to say ...

 

If range "c2" on sheet1 = "yes"

then

sheet2 range "c4" = yes

 

does that makes sense?

[stevie_b]

That's right, but the syntax is like this:

 

If Sheets("Sheet1").Range("C2").Value = "yes" Then ....

 

Basically, the code drills down through the layers of an Excel spreadsheet: first it specifies the sheet, then the cell in that sheet. If you want to go wild, you can specify the spreadsheet itself if you've got more than one open (only do this if you need to: it's pointless otherwise).