Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
Hi,
does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
You should use union.
Set r1 = range1 Set r2 = range2 Set myselection = Union(r1, r2) "gr8guy" wrote in message ... Hi, does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
Proof this
to be defined. like mymultiRange=Range("D7,D7:D12,F7:F11,E18:F20,H10:I 15") Not Union Fer "gr8guy" escribió en el mensaje ... Hi, does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
You can use union since you are getting references to the found cells (the
variable c) sub findtheperson() dim WS as worksheet Dim c as Range, rng as Range dim sname as string, firstaddress as String dim Cnt as Long Cnt=0 set WS=Worksheets("Sheet1") Dim rng as Range sname=LCase(Inputbox("Enter the name to Select: ")) With WS.Cells set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 if rng is nothing then set rng = c else set rng = Union(rng,c) end if Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress rng.Select End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End sub -- Regards, Tom Ogilvy "gr8guy" wrote in message ... Hi, does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
This code should do what you want:-
'--------------------------------------------------------- Sub findtheperson() Dim WS As Worksheet Dim sname As String Dim Cnt As Long Dim RangeList As String '-------------------------------------- Set WS = Worksheets("Sheet1") Cnt = 0 RangeList = "" sname = LCase(InputBox("Enter the name to Count: ")) '- With WS.Cells Set c = .Find(sname, LookIn:=xlValues) If Not c Is Nothing Then firstaddress = c.Address Do Cnt = Cnt + 1 RangeList = RangeList & c.Address & "," Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstaddress End If RangeList = Left(RangeList, Len(RangeList) - 1) ActiveSheet.Range(RangeList).Select MsgBox "The Name selected: " & sname & ", Name Count: " & Cnt End With End Sub '------------------------------------------------------------------- "gr8guy" wrote in message ... Hi, does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
This will fail if the string gets too big. Only a concern if there are a
lot of discontiguous cells. -- Regards, Tom Ogilvy "BrianB" wrote in message om... This code should do what you want:- '--------------------------------------------------------- Sub findtheperson() Dim WS As Worksheet Dim sname As String Dim Cnt As Long Dim RangeList As String '-------------------------------------- Set WS = Worksheets("Sheet1") Cnt = 0 RangeList = "" sname = LCase(InputBox("Enter the name to Count: ")) '- With WS.Cells Set c = .Find(sname, LookIn:=xlValues) If Not c Is Nothing Then firstaddress = c.Address Do Cnt = Cnt + 1 RangeList = RangeList & c.Address & "," Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstaddress End If RangeList = Left(RangeList, Len(RangeList) - 1) ActiveSheet.Range(RangeList).Select MsgBox "The Name selected: " & sname & ", Name Count: " & Cnt End With End Sub '------------------------------------------------------------------- "gr8guy" wrote in message ... Hi, does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - selecting multiple cells
Thanks Tom,
Instead of taking With WS.Range("a1:iv65536"), With WS.Cells works fine & better! yes, i do see the previous solution would fail at one point if there are a lot of discontagious cells, if string is too large. But thanks to Brian Also! Thanks a lot! Thanks a Bunch! Rgds, Eijaz "Tom Ogilvy" wrote in message ... This will fail if the string gets too big. Only a concern if there are a lot of discontiguous cells. -- Regards, Tom Ogilvy "BrianB" wrote in message om... This code should do what you want:- '--------------------------------------------------------- Sub findtheperson() Dim WS As Worksheet Dim sname As String Dim Cnt As Long Dim RangeList As String '-------------------------------------- Set WS = Worksheets("Sheet1") Cnt = 0 RangeList = "" sname = LCase(InputBox("Enter the name to Count: ")) '- With WS.Cells Set c = .Find(sname, LookIn:=xlValues) If Not c Is Nothing Then firstaddress = c.Address Do Cnt = Cnt + 1 RangeList = RangeList & c.Address & "," Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstaddress End If RangeList = Left(RangeList, Len(RangeList) - 1) ActiveSheet.Range(RangeList).Select MsgBox "The Name selected: " & sname & ", Name Count: " & Cnt End With End Sub '------------------------------------------------------------------- "gr8guy" wrote in message ... Hi, does anybody know through VBA how to select mutliple cells in different columns & rows together. the manual method is to press cntrl key & click the cells. the code i have written asks for a text input to search similar text(names) spread in different columns & rows in a worksheet & gives the count for the no. of names found. sub findtheperson() dim WS as worksheet set WS=Worksheets("Sheet1") dim sname as string dim Cnt as Long Cnt=0 With WS sname=LCase(Inputbox("Enter the name to Count: ")) With WS.Range("a1:iv65536") set c = .Find(sname,Lookin:=xlValues) If Not c Is Nothing Then firstaddress=c.address Do Cnt=Cnt+1 ' Cells.SpecialCells(xlCellTypeConstants).Select Set c= .FindNext(c) Loop While Not c Is Nothing And c.address < firstaddress End If MsgBox "The Name selected: "& sname &", Name Count: " & Cnt End With End With End sub This just finds the count of the no of occurences of the similar names in the worksheet, but does not do a multi-select. Can use ColorIndex property to color code the found names, but donot want to do that, instead do a multi-select. Cant use SpecialCells property as it selects all names on the sheet & also cannot use Union Method as it requires the more than 1 ranges to be defined. like mymultiRange=Union(Range1,Range2,....) Any ideas????????? Rgds, Eijaz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select multiple adjacent cells of multiple cells without selecting | New Users to Excel | |||
Select multiple adjacent cells of multiple cells without selecting | Excel Worksheet Functions | |||
Range selecting cells | Excel Discussion (Misc queries) | |||
Selecting a Range of cells in VBA | Excel Programming | |||
Selecting a range of cells | Excel Programming |