VBA Search Help Please

bloodmilksky

Board Regular
Joined
Feb 3, 2016
Messages
202
Hi Guys I was just wondering if anyone knows how to use the below code or even a different code to return groups of ranges as I am having real trouble get the below to work.


' Defines LastRow as last row of column C of the Lenses sheet containing data
LastRow = Sheets("Lenses").Cells(Rows.Count, "C").End(xlUp).Row
' If target cell is A1 then...
If Not Intersect(Target, Range("A29")) Is Nothing Then
' If target value is not blank then...
If Target.Value <> "" Then
' Sets FindString as the value of A1 (case insensitive)
FindString = UCase(Target.Value)
' Sets search range as Solutions sheet C1 to last row
Set sRange = Sheets("Lenses").Range("B29:D36,E29:G36,H29:J36,B37:D44,E37:G44,H37:J44,H45:J52,E44:G52,B45:D52")
' Set variable RowNo as 1
RowNo = 29
' For each cell in the search range
For Each Cell In sRange
' If the cell contains the FindString value (case insensitive) then...
If InStr(1, UCase(Cell.Value), FindString) Then
' Copy columns A:D of the cell row from Solutions and paste to the current RowNo of column B of Menu
Sheets("Lenses").Range("Lenses1").Copy Range("Return")
' Increase RowNo by 1 to account for the new data
RowNo = RowNo + 1
End If
' Check next cell in search range
Next Cell
' If the name was not found then...
If Range("B29") = "" Then
' Display an error stating the name is not in the list
MsgBox "Specified name does not exist", vbOKOnly, "Attention!"
' Clear the contents of A1
Target.ClearContents
' Reselect cell A1
Range("A10").Select
End If
' Else if A1 is empty...
Else
' Clear the contents of B:E on the Menu sheet
Range("B29:G70").ClearContents
End If
End If
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
Hi

Very hard to understand from the code what you're intending

Can you state what needs to happen, but try to avoid mixing it with how you think it should be designed
 

bloodmilksky

Board Regular
Joined
Feb 3, 2016
Messages
202
Hi,

so my aim to to search for a range (block) of cells and return that range like b29:d36 on lenses. Return is where i was having the searchable range returned to. but i wanted to do it with alot of cells like B29:D36,E29:G36,H29:J36,B37:D44,E37:G44,H37:J44,H45:J52,E44:G52,B45:D52. each range has alot of data init that too long to return in a row form. does this kind of make sense?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top