PDA

View Full Version : Excel 2003 question ( using If )


JumperG
04-26-2010, 12:40 AM
HI Guys,

New to here and VBA), so please be gentle.

Here is what I would like to achieve.

If cell A1 on current sheet, matches cell A1 on another sheet, then return the value of B1 of the other sheet into cell B1 on current sheet.

Current Sheet A1: 00050670B1:Other SheetA1: 00050670B1:052814

Many thanks in advance!
Bob Phillips
04-26-2010, 12:55 AM
No need for VBA, just use a formula in B1 of

=IF(A1='00050670B1'!A1,'00050670B1'!B1,"")
JumperG
04-26-2010, 12:58 AM
Hi XLD,

many thanks for the reply. I do need this in VBA (sitting behind a button). The existing sheet pulls in data and the value in A1 is therefor variable. I would like to be able to click on a button, match the values up and return the values in column B of the other sheet.

I hope that makes sense.
Bob Phillips
04-26-2010, 01:09 AM
With Worksheets("00050670B1")

If .Range("A1").Value2 = Worksheets("00050670B1").Range("A1").Value2 Then

Worksheets("00050670B1").Range("B1").Value2 = .Range("B1").Value2
Else

Worksheets("00050670B1").Range("B1").Value2 = ""
End If
End With
JumperG
04-26-2010, 01:45 AM
With Worksheets("00050670B1")

If .Range("A1").Value2 = Worksheets("00050670B1").Range("A1").Value2 Then

Worksheets("00050670B1").Range("B1").Value2 = .Range("B1").Value2
Else

Worksheets("00050670B1").Range("B1").Value2 = ""
End If
End With


hi XLD,

Assuming ("00050670B1") is the sheet where I need to get the result from, how do I point this to a sheet by path?

ie. This sheet is currently in C:\Temp\"sheetname"

Many thanks!
Bob Phillips
04-26-2010, 02:46 AM
You need to open that workbook first.
JumperG
04-26-2010, 04:18 AM
The workbook is open.

Runtime error 9.
Script out of Range
Bob Phillips
04-26-2010, 05:18 AM
Qualify that part of the code with the workbook name (sans path).
JumperG
04-26-2010, 06:07 AM
Qualify that part of the coe with the workbook name (sans path).

Done that, but same error.

I have done some playing about and this seems to get the first step out of the way:

With Workbooks("original.xls").Worksheets("name")

If .Range("D5").Value = Workbooks("copy_from.xls").Worksheets("name").Range("B485").Value Then

.Range("E5").Value = Workbooks("copy_from.xls").Worksheets("name").Range("C485").Value
Else

Workbooks("copy_from.xls").Range("B485").Value = ""
End If

What I would like now (if possible) Is to have if the value in Column D, matches what is on the other sheet (copy_from)in Colum B, for it to return the value that is next to that value in Column C (copy_from) into Colum D.

many thanks for your help so far!