Selecting Multiple Cells In Specific Column

[APElliott]

Help,

Could someone write me a code to select cells in a column with a particular text value.

I would then like to offset the highlighted cell selection to a different column.

Any help would be great

Thankyou

Andrew

 

[xlbo]

Andrew - this loops thru col A looking for a certain text string. It then selects the same rows in colB:

Sub selectdiscontrg()
x = 0
For i = 1 To Range("A65536&quot.End(xlUp).Row

If Range("A" & i).Text = "insert text here" Then
If x < 1 Then
rgSel = &quot;B&quot; & i 'change B to whatever column you want to select
Else
rgSel = rgSel & &quot;,B&quot; & i 'change B to whatever column you want to select
End If
x = x + 1
Else
End If
Next i
Range(rgSel).Select
End Sub

HTH Rgds
~Geoff~

 

[APElliott]

Cheers,

This does work, but the cells i want to selectd are always together, ie s1:s40 and this code selects on a individual basis. So the problem occurs because i was going to make this part of a paste command.

Any ideas

Thanks

Andrew

 

[xlbo]

Right, ok, try this'n then:

Sub SelectEmAll()
With ActiveSheet.Columns(&quot;S&quot
Set c = .Find(&quot;Text To Search For&quot;, Range(&quot;S1&quot, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
fRow = c.Row
Else
MsgBox &quot;No Matching Data Found&quot;
Exit Sub
End If
End With

ctr = WorksheetFunction.CountIf(ActiveSheet.Range(&quot;S2:S&quot; & Range(&quot;A65536&quot.End(xlUp).Row), &quot;Text To Search For&quot

Range(&quot;T&quot; & fRow & &quot;:T&quot; & fRow + ctr - 1).Copy Destination:=Sheets(&quot;Sheet2&quot.Range(&quot;A1&quot' change to whatever column you want to copy and whatever you want the destination to be - this is far quicker than select/copy/select/paste
End Sub

HTH Rgds
~Geoff~

 

[APElliott]

Hi Geoff,

What i've tried to do is Select ie copy subcontractor using the commands on that workbook i sent you yesterday and then insert those rates into another workbook

I've been using this command which again is in the workbook i sent you yesterday:

Private Sub Image13_Click()
msheet = ActiveSheet.Name
Select Case msheet
Case &quot;BoQ&quot;
If Application.CutCopyMode = False Then
MsgBox &quot;Please Select Subcontractor Rates To Paste!&quot;
Else
Range(&quot;s:s&quot.Select
Set Rng = Selection.Find(What:=ComboBox1.Text, After:=ActiveCell, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Rng Is Nothing Then
MsgBox &quot;This Subcontractor Doesn't Exist!&quot;
Exit Sub
End If
Rng.Activate
ActiveCell.Offset(0, -11).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Case Else
MsgBox &quot;Please Select Bill Of Quantities To Enter Rates&quot;
End Select
End Sub

The trouble is that there might be an occasion when the areas are a diiferent size, and i don't want it to allow the paste to complete should this be the case.

A warning message should activate!

Cheers
Andrew

 

[xlbo]

But this code will only copy and paste 1 cell...... how can the areas be different sizes ???

ps - apologies but I had to delete your workbook - network space constraints... Rgds
~Geoff~

 

[APElliott]

The code above only pastes to one cell. The data copied might be several cells long, and it therefore ends up pasting to the same amount of cells. The amount of cells pasted should, & in most cases does match the number of selected reference in column S. It just on the odd occasion that they don't match that i would like it no to paste and warn me instead!

Cheers

Andrew

 

[xlbo]

Not sure I understand how the code is working then -you have no loop to find more than 1 cell and I can't see a copy statement before the paste.....??? Rgds
~Geoff~

 

[APElliott]

The copy statement is in a different code, we like to keep them seperate - some people in the office feel safer doing it this way.

I'll resend the workbook if that's OK. The copy command is on the 'Select Subs' tab, and the paste code is on the 'Enter Subs To BoQ' tab(this second tab is for uses with another workbook though, but i've included the 'BoQ' worksheet in this workbook to show you how it works.

Some users sometimes have alter the references on the 'BoQ' sheet 'Sub Ref' column and forget to alter the corresponding worksheets in this Sub Comp workbook. This is when the problems occur with the copy and paste.

So i need some code to warn me if the sizes are different!

Cheers

Andrew