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

[Steve]

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

 

[Zone]

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]

Hi James,

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

Steve

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

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

 

[Zone]

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

Hi James,

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

Steve

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

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