How 2 refer to 1 spreadsheet using VBA from a diff sheet 3

[belinea2008]

Hi guys,

I have a workbook that I use as a database and different spreadsheets within the workbook contain different data.

I wish to use one sheet as a search form and use code or a macro to search either specific sheets or/and all sheets depending on the user requirements. So for the purpose of this question lets assume the search sheet is called 'frmSearch' and the sheet cotaining the data is called 'shtData' and these are contained in a workbook called 'myData'

I have found some code on a different forum that will perform the search I need BUT it has to be run from within the sheet containing the data.

How do I refer to the cells on the sheet called 'shtData' using code from my search sheet 'frmSearch' and tell the code to display any data or records found back on my search sheet 'shtData'?

I'm afraid I am rather new to VBA so please be gentle with me The code I have found follows below.

Thank you for your help in advance

Code start:

Sub Search()
Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"
FindWhat = Range("E5")
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -1)
Range("E" & Rows.Count).End(xlUp).Offset(0, 1) = Cell
Range("E" & Rows.Count).End(xlUp).Offset(0, 2) = Cell.Offset(, 1)
Range("E" & Rows.Count).End(xlUp).Offset(0, 3) = Cell.Offset(, 2)
End If
Next Cell
End Sub

Code End:

 

[pinkgecko]

From what I understand of your post, you can just use the worksheets("SheetName").range... syntax.

To search the same range in all, you could use something like this:

Sub Search()
   dim FindWhat as string
   dim mySheet as worksheet

   FindWhat = worksheets("frmsearch").Range("E5")

   for each mySheet in activeworkbook.worksheets
      if mysheet.name<>"frmsheet" then
         For Each Cell In mysheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
            'search individual cells/fields here
         Next Cell
      end if
  next mySheet
end sub

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]

 

[belinea2008]

Hi pinkgecko

First let me say thank you for your reply - I really appreciate it.

I thought I'd try the first part of your suggestion to start with and straight away I hit an error with the first line of code. I know I must be doing something wrong.

I added the spreadsheet reference as you suggested and VBA gives a Compile error: Syntax error and turns the following line of code red

CODE:
("CD-C.Full.Details").Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"

I wonder if I could ask for further suggestions.

Thank you in advance

belinea

 

[SkipVought]

Sheets("CD-C.Full.Details").Range("E11").CurrentRegion.ClearContents: Sheets("CD-C.Full.Details").Range("E11") = "Results"

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[belinea2008]

Hi Skip,

Thank you so much for the reply - I really appreciate it but I have run into further problems.

VBA is now highlighting in yellow the row I have enclosed in ** (obviously the ** is not actually part of my code).

I have tried as many solutions as I can before posting for help again but I just cant seem to get a grip with this - any further help would be great.

Regards

Belinea

Code:
Sub Search()
Sheets("frmsearch").Range("E11").CurrentRegion.ClearContents: Sheets("frmsearch").Range("E11") = "Results"
FindWhat = Sheets("frmsearch").Range("E5")

**For Each Cell In Sheets("CD-C.Full.Details.test").Range("B2", Range("B" & Rows.Count).End(xlUp))**

If InStr(Cell, FindWhat) <> 0 Then
Sheets("CD-C.Full.Details.test").Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -1)
Sheets("CD-C.Full.Details.test").Range("E" & Rows.Count).End(xlUp).Offset(0, 1) = Cell
Sheets("CD-C.Full.Details.test").Range("E" & Rows.Count).End(xlUp).Offset(0, 2) = Cell.Offset(, 1)
Sheets("CD-C.Full.Details.test").Range("E" & Rows.Count).End(xlUp).Offset(0, 3) = Cell.Offset(, 2)
End If
Next Cell
End Sub

 

[belinea2008]

Hi - I have manage by trial and many many errors managed to kind of get this to work but now excel is displaying the result wrong.

Excel is showing it like this:
Results
CD-C 0001
A
B Wham Wake Me Up Before You Go Go Album Mix The Greatest Hits From The 80'S

and i need it to be like this:
CD-C 0001 A B Wham Wake Me Up Before You Go Go Album Mix The Greatest Hits From The 80'S

Any tips on what I am doing wrong?

I have tried as many variations as I can think of but I need help with the next step.

Here is the code I am now using:

<Code Start>
Sub Search()
Sheets("frmsearch").Range("A11").CurrentRegion.ClearContents: Sheets("frmsearch").Range("A11") = "Results"
FindWhat = Sheets("frmsearch").Range("E5")

For Each Cell In Sheets("Data").Range("D2", Sheets("Data").Range("D" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then

Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -3)
Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -2)
Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -1)
Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Cell
Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Cell.Offset(, 1)
Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Cell.Offset(, 2)
Sheets("frmsearch").Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Cell.Offset(, 3)

End If
Next Cell
End Sub
<code end>


Thank you in advance

 

[SkipVought]

With Sheets("CD-C.Full.Details.test")
    For Each Cell In .Range(.[B2], .[B2].End(xlDown))
        If InStr(Cell, FindWhat) <> 0 Then
        
            With Sheets("frmsearch")
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -3).Value
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -2).Value
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -1).Value
                .[A65536].End(xlUp).Offset(0, 1).Value = Cell.Value
                .[A65536].End(xlUp).Offset(0, 2).Value = Cell.Offset(0, 1).Value
                .[A65536].End(xlUp).Offset(0, 3).Value = Cell.Offset(0, 2).Value
                .[A65536].End(xlUp).Offset(0, 4).Value = Cell.Offset(0, 3).Value
            End With
        
        End If
    Next
End With

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[belinea2008]

Hi Skip,

Thank you very much for both your time and reply.

Where exactly do I place your code?

Forgive my ignorance but I am trying to learn my way around.

I have tried placing it within my own code but it produces a comple error?

Thanks again

 

[PHV]

comple error
Any chance you could say on which line of code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[belinea2008]

Hi PHV,

sorry I was trying to keep my questions short but I will explain further.

I have incorporated Skips code as best I could without further direction but I just know I have got it wrong.

I no longer get a compile error but Excel is still not displaying the data from the data returned from the macro correctly...

so, what I get now is:

Results
CD-C 0001
A
B Wham Wake Me Up Before You Go Go Album Mix The Greatest Hits From The 80'S
CD-C 0004
2
5 Wham Last Christmas The Greatest Hits From The 80'S

But what I need is:
Results
CD-C 0001 A B Wham Wake Me Up Before You Go Go Album Mix The Greatest Hits From The 80'S
CD-C 0004 2 5 Wham Last Christmas The Greatest Hits From The 80'S


Thanks again

Belinea

This is my new code incorporating Skips suggestion

Sub Search()
Sheets("frmsearch").Range("A11").CurrentRegion.ClearContents: Sheets("frmsearch").Range("A11") = "Results"
FindWhat = Sheets("frmsearch").Range("E5")

With Sheets("CD-C.Full.Details.test")
    For Each Cell In .Range(.[D2], .[D2].End(xlDown))
        If InStr(Cell, FindWhat) <> 0 Then
        
            With Sheets("frmsearch")
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -3).Value
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -2).Value
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -1).Value
                .[A65536].End(xlUp).Offset(0, 1).Value = Cell.Value
                .[A65536].End(xlUp).Offset(0, 2).Value = Cell.Offset(0, 1).Value
                .[A65536].End(xlUp).Offset(0, 3).Value = Cell.Offset(0, 2).Value
                .[A65536].End(xlUp).Offset(0, 4).Value = Cell.Offset(0, 3).Value
            End With
        
        End If
    Next
End With
End Sub

 

[PHV]

...
            With Sheets("frmsearch")
                .[A65536].End(xlUp).Offset(1, 0).Value = Cell.Offset(0, -3).Value
                .[A65536].End(xlUp).Offset([!]0, 1[/!]).Value = Cell.Offset(0, -2).Value
                .[A65536].End(xlUp).Offset([!]0, 2[/!]).Value = Cell.Offset(0, -1).Value
                .[A65536].End(xlUp).Offset(0, [!]3[/!]).Value = Cell.Value
                .[A65536].End(xlUp).Offset(0, [!]4[/!]).Value = Cell.Offset(0, 1).Value
                .[A65536].End(xlUp).Offset(0, [!]5[/!]).Value = Cell.Offset(0, 2).Value
                .[A65536].End(xlUp).Offset(0, [!]6[/!]).Value = Cell.Offset(0, 3).Value
            End With
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[belinea2008]

Hi PHV,

Thank you so much.

Your changes to Skips code work perfectly. I would never have been able to work it out myself - its just too complicated for me with my current level of Excel/VBA knowledge.

I contineue to try to learn from the posts in this forum and with the help from people such as yourself and Skip.

Thank you again.

Belinea

 

[belinea2008]

Skip,

Just wanted to thank you for your original code suggestion.

Although my current knowledge of Excel / VBA would not allow me to use it correctly, PHV was kind enough to help me.

Thank you again so much and I have no doubt that as I try to learn more, I will have more questions and then hopefully I may be able to help others in return.

You guys do such a good job in this forum.

Belinea