Open Workbook, Go To Worksheet ABC, Find XYZ In Column B
 

Workbook named BaseWorkbook is open.

How do I write the following in Excel VBA:
Open C:\MyFolder\MyWorkBook.xls
Is there a worksheet named "ABC"
If No Then
Put text "No 'ABC'" in D4 of BaseWorkbook
End
If Yes
Go to "ABC"
Is the text "XYZ" in Column B
If No Then
Put text "No 'XYZ'" in D4 of BaseWorkbook
End
If Yes
Put value in column L of row containing "XYZ"
in D2 of BaseWorkbook
End

Thanks!

Steve

Open Workbook, Go To Worksheet ABC, Find XYZ In Column B
 

Steve, Copy this code and paste in a standard module of the base workbook.
Code assumes the base workbook's sheet where you want to put the results is
active.
If you don't want MyWorkbook closed, remove or rem out the lines ending in
..Close.
HTH, James

Sub ABCXYZ()
Dim A As Worksheet, B As Worksheet, c As Range
Set B = ActiveSheet
Workbooks.Open "MyWorkbook.xls"
On Error Resume Next
Set A = ActiveWorkbook.Worksheets("ABC")
If Err < 0 Then
B.[d4] = "No ABC"
Workbooks("MyWorkbook.xls").Close
Exit Sub
End If
On Error GoTo 0
Set c = A.Columns(2).Find("XYZ")
If c Is Nothing Then
B.[d4] = "No XYZ"
Else
B.[d2] = A.Cells(c.Row, "L")
End If
Workbooks("MyWorkbook.xls").Close
End Sub

" Steve" wrote in message
...
Workbook named BaseWorkbook is open.

How do I write the following in Excel VBA:
Open C:\MyFolder\MyWorkBook.xls
Is there a worksheet named "ABC"
If No Then
Put text "No 'ABC'" in D4 of BaseWorkbook
End
If Yes
Go to "ABC"
Is the text "XYZ" in Column B
If No Then
Put text "No 'XYZ'" in D4 of BaseWorkbook
End
If Yes
Put value in column L of row containing "XYZ"
in D2 of BaseWorkbook
End

Thanks!

Steve

Open Workbook, Go To Worksheet ABC, Find XYZ In Column B
 

Hi James,

Thank you very much for the help! Have a nice weekend.

Steve


"Zone" wrote in message
...
Steve, Copy this code and paste in a standard module of the base workbook.
Code assumes the base workbook's sheet where you want to put the results
is active.
If you don't want MyWorkbook closed, remove or rem out the lines ending in
.Close.
HTH, James

Sub ABCXYZ()
Dim A As Worksheet, B As Worksheet, c As Range
Set B = ActiveSheet
Workbooks.Open "MyWorkbook.xls"
On Error Resume Next
Set A = ActiveWorkbook.Worksheets("ABC")
If Err < 0 Then
B.[d4] = "No ABC"
Workbooks("MyWorkbook.xls").Close
Exit Sub
End If
On Error GoTo 0
Set c = A.Columns(2).Find("XYZ")
If c Is Nothing Then
B.[d4] = "No XYZ"
Else
B.[d2] = A.Cells(c.Row, "L")
End If
Workbooks("MyWorkbook.xls").Close
End Sub

" Steve" wrote in message
...
Workbook named BaseWorkbook is open.

How do I write the following in Excel VBA:
Open C:\MyFolder\MyWorkBook.xls
Is there a worksheet named "ABC"
If No Then
Put text "No 'ABC'" in D4 of BaseWorkbook
End
If Yes
Go to "ABC"
Is the text "XYZ" in Column B
If No Then
Put text "No 'XYZ'" in D4 of BaseWorkbook
End
If Yes
Put value in column L of row containing "XYZ"
in D2 of BaseWorkbook
End

Thanks!

Steve

Open Workbook, Go To Worksheet ABC, Find XYZ In Column B
 

Glad to help! By the way, I just noticed Find "remembers" previous
settings, so this code will work best if you don't change the Find options
before running the code. I posted a new thread about that. Cheers, James
" Steve" wrote in message
...
Hi James,

Thank you very much for the help! Have a nice weekend.

Steve


"Zone" wrote in message
...
Steve, Copy this code and paste in a standard module of the base
workbook.
Code assumes the base workbook's sheet where you want to put the results
is active.
If you don't want MyWorkbook closed, remove or rem out the lines ending
in .Close.
HTH, James

Sub ABCXYZ()
Dim A As Worksheet, B As Worksheet, c As Range
Set B = ActiveSheet
Workbooks.Open "MyWorkbook.xls"
On Error Resume Next
Set A = ActiveWorkbook.Worksheets("ABC")
If Err < 0 Then
B.[d4] = "No ABC"
Workbooks("MyWorkbook.xls").Close
Exit Sub
End If
On Error GoTo 0
Set c = A.Columns(2).Find("XYZ")
If c Is Nothing Then
B.[d4] = "No XYZ"
Else
B.[d2] = A.Cells(c.Row, "L")
End If
Workbooks("MyWorkbook.xls").Close
End Sub

" Steve" wrote in message
...
Workbook named BaseWorkbook is open.

How do I write the following in Excel VBA:
Open C:\MyFolder\MyWorkBook.xls
Is there a worksheet named "ABC"
If No Then
Put text "No 'ABC'" in D4 of BaseWorkbook
End
If Yes
Go to "ABC"
Is the text "XYZ" in Column B
If No Then
Put text "No 'XYZ'" in D4 of BaseWorkbook
End
If Yes
Put value in column L of row containing "XYZ"
in D2 of BaseWorkbook
End

Thanks!

Steve