If then statement

[max1565]

I am new to vba and i have a problem. I am trying to compare two cells (b1 and c30 on sheet1) and if the are equal then i want to take a value from a third cell (c35 on shee1) and place it in a fourth cell(b2 on sheet data).

Heres what I thought would do it.

sheets("sheet1&quot.select
if Range("b1&quot.value = Range("c30&quot.value then
Range("c35&quot.copy

Sheets("data&quot.Range("b2&quot.value = sheets("sheet1&quot.Range("c35&quot.value
End if

Could someone please tell me what I am doing wrong?

Thanks

 

[ETID]

sheets("sheet1&quot.select
if Range("b1&quot.value = Range("c30&quot.value then Sheets("data&quot.Range("b2&quot.value = sheets("sheet1&quot.Range("c35&quot.value
End if

 

[Zathras]

I wouldn't say you're doing anything wrong. After all, your code works. It's just that you are maybe doing too much. The .copy operation is thrown away. Here's one way to copy the value:
[blue]

Sub test()
  With Sheets("Sheet1")
    If .Range("B1") = .Range("C30") Then
      Sheets("data").Range("B2") = .Range("C35")
    End If
  End With
End Sub

[/color]

Just for amusement, if you assign range names to the four critical cells, you could do it with just one line of code, this way:
[blue]

Sub AnotherWay()
  If [FACTOR1] = [FACTOR2] Then [TOHERE] = [COPYFROM]
End Sub

[/color]

 

[max1565]

ETID

Thanks for your response. I entered the following code and it worked for a while and the quit working. Any ideas on what could be wrong?

Thanks

sheets("sheet1&quot.select
if Range("b1&quot.value = Range("c30&quot.value then
Sheets("data&quot.Range("b2&quot.value = sheets("sheet1&quot.Range("c35&quot.value
End if

 

[ETID]

As Zathras pointed out...have you tried dropping the .value and .select
from your code?

i.e.


if sheets("sheet1&quot.Range("b1&quot = sheets("sheet1&quot.Range("c30&quotthen Sheets("data&quot.Range("b2&quot = sheets("sheet1&quot.Range("c35&quot

 

[max1565]

ETID

SORRY TO BE SO MUCH TROUBLE BUT I PUT THE BELOW CODE IN AND IT RAN FINE FOR ONE TIME, THEN WOULD NOT WORK ANYMORE. aNY MORE SUGGESTIONS?

tHANKS

 

[ETID]

can you post the entire sub that have written?

 

[max1565]

i APPRECIATE YOUR HELP. tHE SUB IS AS FOLLOWS:

Sub VDCOGS()
'''''TRANSFER VISTA DIAMANTE COST OF''''''''''''''''
''''''''GOODS SOLD TO DATA SHEET'''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''LOT 1
If Sheets("VISTA DIAMANTE&quot.Range("C30&quot = Sheets("VISTA DIAMANTE&quot.Range("B1&quot Then
Sheets("DATA&quot.Range("F8&quot = Sheets("VISTA DIAMANTE&quot.Range("C45&quot
End If
''''LOT 2
If Sheets("VISTA DIAMANTE&quot.Range("D30&quot = Sheets("VISTA DIAMANTE&quot.Range("B1&quot Then
Sheets("DATA&quot.Range("F9&quot = Sheets("VISTA DIAMANTE&quot.Range("D45&quot
End If

''''LOT 3
If Sheets("VISTA DIAMANTE&quot.Range("E30&quot = Sheets("VISTA DIAMANTE&quot.Range("B1&quot Then
Sheets("DATA&quot.Range("F10&quot = Sheets("VISTA DIAMANTE&quot.Range("E45&quot
End If

''''LOT 4
If Sheets("VISTA DIAMANTE&quot.Range("F30&quot = Sheets("VISTA DIAMANTE&quot.Range("B1&quot Then
Sheets("DATA&quot.Range("F11&quot = Sheets("VISTA DIAMANTE&quot.Range("F45&quot
End If

''''LOT 5
If Sheets("VISTA DIAMANTE&quot.Range("G30&quot = Sheets("VISTA DIAMANTE&quot.Range("B1&quot Then
Sheets("DATA&quot.Range("F12&quot = Sheets("VISTA DIAMANTE&quot.Range("G45&quot
End If

Application.Run "VISTA.xls!vdLOTSALES"

End Sub

I CAN EMAIL THE FILE TO YOU IF IT WOULD HELP

tHANKS!!!

 

[ETID]

Is this open when you try to run a second time?

Application.Run "VISTA.xls!vdLOTSALES"

 

[max1565]

tHANKS ETID FOR ALL OF YOUR HELP. i FINALLY FIGURED IT OUT. tHE CELLS i WAS COMPARING TO SEE IF THEY WERE EQUAL WERE NUMBERS. i HAD FAILED TO FORMAT THOSE CELLS AS NUMBERS. oNCE i FORMATED THEM, EVERY THING WORKED.

THANKS A LOT

 

[max1565]

WELL I THOUGHT IT WAS WORKING OKAY. FOR SOME REASON WHEN IT COMPARES THE TWO CELLS (WHICH CONTAINS NUMBERS) IT IS NOT RECOGNIZING THEM AS EQUAL. tHE CELLS ARE FILLED FROM A TEXTBOX ON A USERFORM.

i CAN GO TO THE SHEET AND ENTER THE SAME NUMBER IN BOTHE CELLS AND IT WORKS.

iS IT SOMETHING ABOUT THE TEXTBOX THAT COULD BE MESSING THINGS UP?

tHANKS

 

[ETID]

try enclosing all your references in the trim statement



trim(Sheets("VISTA DIAMANTE&quot.Range("G30&quot) and so on


for each one

 

[max1565]

tHANKS A BUNCH ETID, THAT APPEARS TO HAVE CORRECTED THE PROBLEM

THANKS

 

[ETID]

Those dang trailing spaces........

 

[xlbo]

Max1565 - tiny tip. Generally, any posting in CAPS is used to emphasise a point or to shout. It is general practice to not use CAPS for your whole post. Try to use lower case and only use CAPS for emphasis.

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[max1565]

sorry geoff, didn't mean to hurt your ears

 

[ETID]

It looks as though the caps lock got him...happens to me every now again.

...note the small t at the start of each sentance.

 

[xlbo]

Indeed - was just a friendly note - no offense meant or taken

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes