[L. Howard]

With sheet names in a drop down in Cell A2 what is the generic

With Sheets("Sheet name in Drop Down")
'Do stuff
End With

and

Set Rng = Sheets("Sheet name in Drop Down").Range("D5", Range("D5").End(xlDown))


I want to Find a String on any one of the sheets in the drop down and return an offset value to main sheet.

I can write the Find but am not sure how to ref the sheet from the drop down as the search sheet.

Thanks,
Howard

[Claus Busch]

Hi Howard,

Am Thu, 17 Nov 2016 22:52:09 -0800 (PST) schrieb L. Howard:

With sheet names in a drop down in Cell A2 what is the generic

With Sheets("Sheet name in Drop Down")
'Do stuff
End With

and

Set Rng = Sheets("Sheet name in Drop Down").Range("D5", Range("D5").End(xlDown))

I want to Find a String on any one of the sheets in the drop down and return an offset value to main sheet.

your drop down in Sheet1 cell A1. Then try:

Sub Test()
Dim shN As String
Dim LRow As Long
Dim myRng As Range

shN = Sheets("Sheet1").Range("A1")
With Sheets(shN)
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
Set myRng = .Range("D5:D" & LRow)
'Do stuff
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

[Claus Busch]

Hi Howard,

Am Fri, 18 Nov 2016 08:09:30 +0100 schrieb Claus Busch:

your drop down in Sheet1 cell A1. Then try:

sorry, I didn't read carefully.

Try:

Sub Test()
Dim varSh As Variant
Dim LRow As Long, i As Long
Dim myRng As Range
Dim myStr As String

varSh = Split(Sheets("Sheet1").Range("A1").Validation.Form ula1, ";")

For i = LBound(varSh) To UBound(varSh)
With Sheets(varSh(i))
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
Set myRng = .Range("D5:D" & LRow)
'Do stuff
End With
Next
End Sub

Check out what separator you have in the validation list. In german
system it is a semicolon. Perhaps it is a comma in your system.


Regards
Claus B.
--
Windows10
Office 2016

[L. Howard]

On Friday, November 18, 2016 at 12:15:13 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 18 Nov 2016 08:09:30 +0100 schrieb Claus Busch:

your drop down in Sheet1 cell A1. Then try:

sorry, I didn't read carefully.

Try:

Sub Test()
Dim varSh As Variant
Dim LRow As Long, i As Long
Dim myRng As Range
Dim myStr As String

varSh = Split(Sheets("Sheet1").Range("A1").Validation.Form ula1, ";")

For i = LBound(varSh) To UBound(varSh)
With Sheets(varSh(i))
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
Set myRng = .Range("D5:D" & LRow)
'Do stuff
End With
Next
End Sub

Check out what separator you have in the validation list. In german
system it is a semicolon. Perhaps it is a comma in your system.


Regards
Claus B.


Hi Claus.

Actually, the first one is correct for what I want to do. I like the second for a good example of "for all sheets in drop down".
Thank you very much.

Howard

[bulong]

Chúc bạn đắt hÃ*ng
_________________
bulong móng

https://t.co/1OiBxEhCD2

[bulong]

hay quá đúng cái em đang cần đánh dấu mai em call bác
_________________
kinky videos

https://t.co/Q7LrbOZYf0