PDA

View Full Version : Search value as the part of the Sheetname



sindhuja
10-08-2009, 11:50 AM
Hi All,

Any help on my below requirement pls...

I have a sheet with numeric values in column A of a sheet.
Also I have a workbook with multiple worksheet.

For ex in a workbook "Data" I have a sheet named sheet1 with the values in column A - column G

For example for al the values in the column A we need to search for the sheetname with that value in the sheetname in a different workbook.
For example the value of 313 in data workbook. There is a sheet named "car 313" in the workbook with multiple sheet.

Now the sheet car 313 should be activated if the value is 313.
I have tried the below coding. But cannot be done as it searches for the exact sheetname as the find value.

Its giving me " not found " when the find value is 313 though "car 313" is found in the workbook.


Sub OpenandFind()
Dim Wsht As Worksheet
Dim SLookfor As String
Dim SFoundIt As String
Application.ScreenUpdating = False
'Value to look for
SLookfor = Sheets("Sheet1").Range("A1")
'Open workbook to look in
Workbooks.Open FileName:="C:\My Documents\Doodlings\Book1.xls"
'Look through each sheet until found
For Each Wsht In ActiveWorkbook.Worksheets
On Error Resume Next
SFoundIt = Wsht.Cells.Find _
(What:=SLookfor, after:=Wsht.Cells(1, 1)).Address
'Value found show sheet name and cell address
If SFoundIt <> "" Then
Application.ScreenUpdating = True
MsgBox "Found " & SLookfor & " on " & Wsht.Name _
& " cell " & SFoundIt, vbInformation
Application.Goto Range(SFoundIt), True
End
End If
Next Wsht
Application.ScreenUpdating = True
'Value not found
MsgBox "Cannot find it", vbInformation
End Sub


Hope i made it clear...
Any help on this will be highly helpful.

-Sindhuja

MaximS
10-11-2009, 03:39 AM
It is not perfectly clear whether you looking for a Sheet name, a Value within Sheet or both.

For finding given Value within Sheet Names you can use following:


Sub OpenandFind()

Dim Wb As Workbook
Dim Wsht As Worksheet
Dim SLookfor As String
Dim SFoundIt As String
Dim x As String

Application.ScreenUpdating = False
'Value to look for

SLookfor = Sheets("Data").Range("A1")
'Open workbook to look in
Set Wb = Workbooks.Open Filename:="C:\My Documents\Doodlings\Book1.xls"
'Look through each sheet until found
For Each Wsht In Wb
On Error Resume Next
x = Application.Find(SLookfor, Wsht.Name, 1)

If x <> "" Then
SFoundIt = Wsht.Name
'Value found show sheet name
Application.ScreenUpdating = True
Wb.Activate
Wsht.Select
MsgBox "Value '" & SLookfor & "' has been found in Sheet '" _
& SFoundIt & "'", vbInformation
End
End If

On Error GoTo 0

Next Wsht

Application.ScreenUpdating = True
'Value not found
MsgBox "Cannot find it", vbInformation
End Sub

bruinenat
10-12-2009, 05:36 AM
Thank you, was looking for this too

sindhuja
10-12-2009, 08:01 AM
Hi,

Thanks a lot !!

Am looking for the sheetname first. Once I locate the sheet then I need to put the corresponding values in the sheet.

-Sindhuja

mdmackillop
10-12-2009, 08:13 AM
Loop through the sheets to find the name, then do something with it
Option Compare Text
Sub FindSheet()
Dim Sh As Worksheet, Txt As String
Dim ToFind As String
ToFind = "*" & "eet" & "*"
For Each Sh In Sheets
If Sh.Name Like ToFind Then
Txt = Txt & Sh.Name 'Your code
End If
Next
MsgBox Txt
End Sub

sindhuja
10-12-2009, 09:18 AM
Hello…

Have attached the sample sheets.

Have two workbook named data and consol…

From the sheet1 of Data sheet filter the PNR column. Search for the sheet names in the consol worksheet with the search criteria as PNR values.
For example if filtered PNR column the first value to look for is 313. Search for the sheet which contains 313 in the consol sheet.

Then, in the data workbook column D with date as the header. That should be the in the header in the consol after the last used column (ie cell U4 in my example) then for a particular PNR number there will be some FNR number. Extract those details in the specific column of consol 's specific sheet.

Hope am now clear !!

-Sindhuja

sindhuja
10-15-2009, 10:41 AM
Any help on this pls..

-sindhuja

mdmackillop
10-15-2009, 03:42 PM
I'll have a look tomorrow.

sindhuja
10-16-2009, 08:22 AM
Thank you so much...

mdmackillop
10-17-2009, 02:55 AM
Then, in the data workbook column D with date as the header. That should be the in the header in the consol after the last used column (ie cell U4 in my example) then for a particular PNR number there will be some FNR number. Extract those details in the specific column of consol 's specific sheet.
I don't follow what is wanted here. Please show "finished" data with some clearer comments/steps

sindhuja
10-17-2009, 02:56 PM
Hi Md,

I have attached the sample sheet.

Let me example my requirement in two criteria...
Data sheet is the consolidation of all the data which needs to filled in the appropriate sheet.

1) we have PNR and FNR columns in Data sheet.
PNR column value will be the part of sheet name in the consol sheet.

2) Column D in Data sheet will have a date. That should be the column heading in the consol after the last used column. For ex in the Data sheet Column D is with value 10/6/09. Then it should be the column heading after the last used column in the row 4.


2) Filter PNR column , look for the first value (for ex 313). Now search for the sheet name which has the value as 313. Then again filter FNR for each unique value get the corresponding Column D value in the consol sheet.

If we filter PNR first value is 313 and in that 313 we have 313 and 314 FNR values. if there is more than one 313 FNR value then the sum of value should be in the corresponding column of consol sheet. we should loop through all the FNR value for each PNR value.

3) Similarly we have to loop through all the values in the PNR column.
and each PNR value, there will be seperate sheet in the consol sheet.

Hope i made it clear now.
Thanks in advance.... :)


-Sindhuja

sindhuja
10-17-2009, 03:12 PM
Attached is the sample spreadsheet with comments and the expected results.

sindhuja
10-22-2009, 09:23 AM
any assistance pls.....:help :help

sindhuja
10-26-2009, 01:07 PM
Any clue on my requirement...

mdmackillop
10-26-2009, 04:45 PM
I've assumed both books are open.

Option Explicit
Sub GetData()
Dim wbTgt As Workbook
Dim wsTgt As Worksheet
Dim ws As Worksheet
Dim wsData As Worksheet
Dim PNR As New Collection
Dim FNR As New Collection
Dim RngPNR As Range
Dim rngFNR As Range
Dim rngDate As Range
Dim cel As Range
Dim tgt As Range
Dim p, f
Dim tot As Single



Set wbTgt = Workbooks("consol.xls")
Set wsData = Workbooks("Data.xls").Sheets("Sheet1")
Set RngPNR = Range(wsData.Cells(1, 1), wsData.Cells(Rows.Count, 1).End(xlUp))
On Error Resume Next
For Each cel In RngPNR
If IsNumeric(cel) Then PNR.Add cel.Value, CStr(cel.Value)
Next
For Each p In PNR
wsData.Columns(1).AutoFilter , field:=1, Criteria1:=p
Set rngFNR = RngPNR.Offset(, 1).SpecialCells(xlCellTypeVisible)
Set rngDate = rngFNR.Offset(, 2)

For Each cel In rngFNR
If cel <> "FNR" Then FNR.Add cel.Value, CStr(cel.Value)
Next

For Each ws In wbTgt.Worksheets
If ws.Name Like "*" & p Then
Set wsTgt = ws
Exit For
End If
Next

wsTgt.Activate
Set cel = ws.Cells(4, Columns.Count).End(xlToLeft).Offset(, 1)
cel = wsData.Range("D1")

For Each f In FNR
tot = Application.SumIf(rngFNR, f, rngDate)
Set tgt = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
tgt.Value = f
wsTgt.Cells(tgt.Row, cel.Column) = tot
tgt.Interior.ColorIndex = 6
wsTgt.Cells(tgt.Row, cel.Column).Interior.ColorIndex = 6
Next

For Each f In FNR
FNR.Remove 1
Next
wsData.Columns(1).AutoFilter
Next
End Sub

sindhuja
10-29-2009, 06:33 AM
Hi Md...

Thanks for your assistance….

Tried the coding and found the FNR get append to the appropriate sheet.

Per my understanding , as per the below code xlup has been used to find the last used row in a FNR column.

But as I have attached the sample data I have removed the rows below and hence in my original data this does not work.
also my PNR and FNR formatting in my original data is like 00313 and so its not giving me the expected results..

Also my requirement is to fill in the data in the already existing PNR values of consol sheet rather than inserting a new row for each PNR value.


If this is the case, we might have not used the End(xlUp) function….

For Each f In FNR
tot = Application.SumIf(rngFNR, f, rngDate)
Set tgt = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
tgt.Value = f
wsTgt.Cells(tgt.Row, cel.Column) = tot
tgt.Interior.ColorIndex = 6
wsTgt.Cells(tgt.Row, cel.Column).Interior.ColorIndex = 6
Next


Hope I understood the coding right.. Correct me if am wrong...


Hope now its clear..


-Sindhuja

sindhuja
11-02-2009, 09:49 AM
Hi Md :help

sindhuja
11-11-2009, 12:10 PM
Help me Pls...
Am struggling with this...

mdmackillop
11-11-2009, 02:19 PM
also my PNR and FNR formatting in my original data is like 00313 and so its not giving me the expected results..



There is little point in trying to give you a solution if your sample does not match the original data. Please provide a proper sample with formats, dates etc. that match in both workbooks.

sindhuja
11-15-2009, 06:17 AM
Hi,

I have attached the file...
want the data to be filled in the C1 part of the consol sheet...

-Sindhuja

mdmackillop
11-15-2009, 09:27 AM
Now I'm even more confused. You appear to be entering the same result data 3 times under C1, C2 & C3.

sindhuja
11-16-2009, 09:21 AM
Hi Md,

I will be using C1, C2 and C3...

But the Data in the data sheet will be only for C1 and for C2 and C3 i will be using some other reports...

I need to fill only C1 using the data in the Data sheet i have attached..

Now i think i made it clear...

-Sindhuja

sindhuja
11-24-2009, 01:46 PM
Any clue on my requirement....

-sindhuja

mdmackillop
11-24-2009, 04:34 PM
Apologies for the delay.

Option Explicit
Sub GetData()
Dim wbTgt As Workbook
Dim wsTgt As Worksheet
Dim ws As Worksheet
Dim wsData As Worksheet
Dim PNR As New Collection
Dim FNR As New Collection
Dim RngPNR As Range
Dim rngFNR As Range
Dim rngDate As Range
Dim cel As Range
Dim tgt As Range
Dim p, f
Dim tot As Single
Dim Dt, c As Range, Col As Long, Rw As Long


Set wbTgt = Workbooks("consol.xls")
Set wsData = Workbooks("Data.xls").Sheets("Sheet1")
Dt = wsData.Range("D1")
Set RngPNR = Range(wsData.Cells(1, 1), wsData.Cells(Rows.Count, 1).End(xlUp))
On Error Resume Next
For Each cel In RngPNR
If IsNumeric(cel) Then PNR.Add cel.Value, CStr(cel.Value)
Next
For Each p In PNR
wsData.Columns(1).AutoFilter , field:=1, Criteria1:=p
Set rngFNR = RngPNR.Offset(, 1).SpecialCells(xlCellTypeVisible)
Set rngDate = rngFNR.Offset(, 2)

For Each cel In rngFNR
If cel <> "FNR" Then FNR.Add cel.Value, CStr(cel.Value)
Next

For Each ws In wbTgt.Worksheets
If ws.Name Like "*" & CInt(p) Then
Set wsTgt = ws
Exit For
End If
Next

wsTgt.Activate

Set c = wsTgt.Rows(4).Find(Dt)
If Not c Is Nothing Then
Col = c.Column
Else
Col = wsTgt.Cells(4, Columns.Count).End(xlToLeft).Column + 1
wsTgt.Cells(4, Col) = Dt
End If

Rw = wsTgt.Columns(3).Find("=", LookIn:=xlFormulas, lookat:=xlPart).Row

For Each f In FNR
tot = Application.SumIf(rngFNR, f, rngDate)
Set tgt = Range(wsTgt.Cells(5, 1), wsTgt.Cells(Rw, 1))
Set tgt = tgt.Find(f)
If Not tgt Is Nothing Then
wsTgt.Cells(tgt.Row, Col) = tot
wsTgt.Cells(tgt.Row, Col).Interior.ColorIndex = 6
Else
Rw = Cells(Rw, 1).End(xlUp)(2).Row
wsTgt.Cells(Rw, Col) = tot
wsTgt.Cells(Rw, 1) = f
wsTgt.Cells(Rw, 1).Interior.ColorIndex = 6
wsTgt.Cells(Rw + 1, 1).EntireRow.Insert
End If
tgt.Interior.ColorIndex = 6
Next

For Each f In FNR
FNR.Remove 1
Next
wsData.Columns(1).AutoFilter
Next
End Sub

sindhuja
12-16-2009, 01:29 PM
Thanks Md,

Again its not picking up the value from data sheet..
showing up the same value in all the sheets..

-Sindhuja