Hi there,

I have no VBA-knowledge whatshowever.
I was wondering if the following would be possible:

Say:
In A2:A500 I have names filled in: Bob, Jen, Tom ,....

Can I select Cells in column H:K when "Jen" is in that row?
eg.

A10 = "Jen" select range H10:K10
A67 = "Jen" select range H67:K67

and every other "row-"range when my name appears in the first Column?


(Would it be possible to match the range (A2:A500) with a name I give
in in an Inputbox? SO that I do not have to change the name every time
in the code itself?)
Hm many questions...

All help really appreciated!!
Jen

hi,
are we talking multiple selects? if so, how many? once selected....then what?

Hm many questions...

FSt1


"Jen" wrote:

> Hi there,
>
> I have no VBA-knowledge whatshowever.
> I was wondering if the following would be possible:
>
> Say:
> In A2:A500 I have names filled in: Bob, Jen, Tom ,....
>
> Can I select Cells in column H:K when "Jen" is in that row?
> eg.
>
> A10 = "Jen" select range H10:K10
> A67 = "Jen" select range H67:K67
>
> and every other "row-"range when my name appears in the first Column?
>
>
> (Would it be possible to match the range (A2:A500) with a name I give
> in in an Inputbox? SO that I do not have to change the name every time
> in the code itself?)
> Hm many questions...
>
> All help really appreciated!!
> Jen
>
>

Sub SelectCells()
On Error Resume Next
iRow = Application.Match("Jen",Columns(1),0)
On Error Goto 0
If iRow > 0 Then
Cells(iRow,"H").Resize(,4).Select
End If
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi there,
>
> I have no VBA-knowledge whatshowever.
> I was wondering if the following would be possible:
>
> Say:
> In A2:A500 I have names filled in: Bob, Jen, Tom ,....
>
> Can I select Cells in column H:K when "Jen" is in that row?
> eg.
>
> A10 = "Jen" select range H10:K10
> A67 = "Jen" select range H67:K67
>
> and every other "row-"range when my name appears in the first Column?
>
>
> (Would it be possible to match the range (A2:A500) with a name I give
> in in an Inputbox? SO that I do not have to change the name every time
> in the code itself?)
> Hm many questions...
>
> All help really appreciated!!
> Jen
>

This will do the selection, but unless you add some
code, where the MsgBox is, all it will do is walk down
Column A doing selections and nothing else.

lr = Cells(Rows.Count).End(xlUp).Row
For Each i In Range("A2:A" & lr)
If i = "Jan" Then
Range ("H" & i & ":K" & i).Select
End If
MsgBox "H Through K of this row selected"
Next


"Jen" wrote:

> Hi there,
>
> I have no VBA-knowledge whatshowever.
> I was wondering if the following would be possible:
>
> Say:
> In A2:A500 I have names filled in: Bob, Jen, Tom ,....
>
> Can I select Cells in column H:K when "Jen" is in that row?
> eg.
>
> A10 = "Jen" select range H10:K10
> A67 = "Jen" select range H67:K67
>
> and every other "row-"range when my name appears in the first Column?
>
>
> (Would it be possible to match the range (A2:A500) with a name I give
> in in an Inputbox? SO that I do not have to change the name every time
> in the code itself?)
> Hm many questions...
>
> All help really appreciated!!
> Jen
>
>

I gave you some bad code there:

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Cells(i, 1) = "Jen" Then
Range(Cells(i, 8), Cells(i, 11)).Select
End If
MsgBox "Cells H through K selected this row"
Next

This will work a lot better. It assumes a header row 1.


"Jen" wrote:

> Hi there,
>
> I have no VBA-knowledge whatshowever.
> I was wondering if the following would be possible:
>
> Say:
> In A2:A500 I have names filled in: Bob, Jen, Tom ,....
>
> Can I select Cells in column H:K when "Jen" is in that row?
> eg.
>
> A10 = "Jen" select range H10:K10
> A67 = "Jen" select range H67:K67
>
> and every other "row-"range when my name appears in the first Column?
>
>
> (Would it be possible to match the range (A2:A500) with a name I give
> in in an Inputbox? SO that I do not have to change the name every time
> in the code itself?)
> Hm many questions...
>
> All help really appreciated!!
> Jen
>
>

On Jun 1, 2:40 am, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> I gave you some bad code there:
>
> lr = Cells(Rows.Count, 1).End(xlUp).Row
> For i = 2 To lr
> If Cells(i, 1) = "Jen" Then
> Range(Cells(i, 8), Cells(i, 11)).Select
> End If
> MsgBox "Cells H through K selected this row"
> Next
>
> This will work a lot better. It assumes a header row 1.
>
>
>
> "Jen" wrote:
> > Hi there,
>
> > I have no VBA-knowledge whatshowever.
> > I was wondering if the following would be possible:
>
> > Say:
> > In A2:A500 I have names filled in: Bob, Jen, Tom ,....
>
> > Can I select Cells in column H:K when "Jen" is in that row?
> > eg.
>
> > A10 = "Jen" select range H10:K10
> > A67 = "Jen" select range H67:K67
>
> > and every other "row-"range when my name appears in the first Column?
>
> > (Would it be possible to match the range (A2:A500) with a name I give
> > in in an Inputbox? SO that I do not have to change the name every time
> > in the code itself?)
> > Hm many questions...
>
> > All help really appreciated!!
> > Jen- Hide quoted text -
>
> - Show quoted text -

Hi All,

Thank you so much for your help!
I was hoping though that I could have a multiple selection though ...
So that all my instances of "Jen" found in the first column would
select all the ranges in column H:K, instead of just the first
instance found?
You see I have difficulties to even properly describe what i
"want".
Hopefully this is possible?!

Thanks again, Jen

Have you thought about using Data|Filter|autofilter instead of selecting the
range? That way, your data will be visible and you won't have to do any
scrolling.

But if you want:

Option Explicit
Sub testme()

Dim myName As String
Dim RngToSearch As Range
Dim myFoundRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

myName = Application.InputBox(Prompt:="Enter a name")
If myName = "" Then
Exit Sub
End If

With ActiveSheet
Set RngToSearch = .Range("a:a")
End With

FirstAddress = ""
Set myFoundRng = Nothing
With RngToSearch
Set FoundCell = .Cells.Find(what:=myName, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox myName & " wasn't found!"
Else
FirstAddress = FoundCell.Address
Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K
Do
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Set myFoundRng = Union(myFoundRng, _
FoundCell.Offset(0, 7).Resize(1, 4))
Loop
myFoundRng.Select
End If
End With

End Sub

Jen wrote:
>
> On Jun 1, 2:40 am, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > I gave you some bad code there:
> >
> > lr = Cells(Rows.Count, 1).End(xlUp).Row
> > For i = 2 To lr
> > If Cells(i, 1) = "Jen" Then
> > Range(Cells(i, 8), Cells(i, 11)).Select
> > End If
> > MsgBox "Cells H through K selected this row"
> > Next
> >
> > This will work a lot better. It assumes a header row 1.
> >
> >
> >
> > "Jen" wrote:
> > > Hi there,
> >
> > > I have no VBA-knowledge whatshowever.
> > > I was wondering if the following would be possible:
> >
> > > Say:
> > > In A2:A500 I have names filled in: Bob, Jen, Tom ,....
> >
> > > Can I select Cells in column H:K when "Jen" is in that row?
> > > eg.
> >
> > > A10 = "Jen" select range H10:K10
> > > A67 = "Jen" select range H67:K67
> >
> > > and every other "row-"range when my name appears in the first Column?
> >
> > > (Would it be possible to match the range (A2:A500) with a name I give
> > > in in an Inputbox? SO that I do not have to change the name every time
> > > in the code itself?)
> > > Hm many questions...
> >
> > > All help really appreciated!!
> > > Jen- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi All,
>
> Thank you so much for your help!
> I was hoping though that I could have a multiple selection though ...
> So that all my instances of "Jen" found in the first column would
> select all the ranges in column H:K, instead of just the first
> instance found?
> You see I have difficulties to even properly describe what i
> "want".
> Hopefully this is possible?!
>
> Thanks again, Jen

--

Dave Peterson

On Jun 1, 2:20 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Have you thought about using Data|Filter|autofilter instead of selecting the
> range? That way, your data will be visible and you won't have to do any
> scrolling.
>
> But if you want:
>
> Option Explicit
> Sub testme()
>
> Dim myName As String
> Dim RngToSearch As Range
> Dim myFoundRng As Range
> Dim FoundCell As Range
> Dim FirstAddress As String
>
> myName = Application.InputBox(Prompt:="Enter a name")
> If myName = "" Then
> Exit Sub
> End If
>
> With ActiveSheet
> Set RngToSearch = .Range("a:a")
> End With
>
> FirstAddress = ""
> Set myFoundRng = Nothing
> With RngToSearch
> Set FoundCell = .Cells.Find(what:=myName, _
> after:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> lookat:=xlWhole, _
> searchorder:=xlByRows, _
> searchdirection:=xlNext, _
> MatchCase:=False)
>
> If FoundCell Is Nothing Then
> MsgBox myName & " wasn't found!"
> Else
> FirstAddress = FoundCell.Address
> Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K
> Do
> Set FoundCell = .FindNext(after:=FoundCell)
> If FoundCell.Address = FirstAddress Then
> Exit Do
> End If
> Set myFoundRng = Union(myFoundRng, _
> FoundCell.Offset(0, 7).Resize(1, 4))
> Loop
> myFoundRng.Select
> End If
> End With
>
> End Sub
>
>
>
>
>
> Jen wrote:
>
> > On Jun 1, 2:40 am, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > I gave you some bad code there:
>
> > > lr = Cells(Rows.Count, 1).End(xlUp).Row
> > > For i = 2 To lr
> > > If Cells(i, 1) = "Jen" Then
> > > Range(Cells(i, 8), Cells(i, 11)).Select
> > > End If
> > > MsgBox "Cells H through K selected this row"
> > > Next
>
> > > This will work a lot better. It assumes a header row 1.
>
> > > "Jen" wrote:
> > > > Hi there,
>
> > > > I have no VBA-knowledge whatshowever.
> > > > I was wondering if the following would be possible:
>
> > > > Say:
> > > > In A2:A500 I have names filled in: Bob, Jen, Tom ,....
>
> > > > Can I select Cells in column H:K when "Jen" is in that row?
> > > > eg.
>
> > > > A10 = "Jen" select range H10:K10
> > > > A67 = "Jen" select range H67:K67
>
> > > > and every other "row-"range when my name appears in the first Column?
>
> > > > (Would it be possible to match the range (A2:A500) with a name I give
> > > > in in an Inputbox? SO that I do not have to change the name every time
> > > > in the code itself?)
> > > > Hm many questions...
>
> > > > All help really appreciated!!
> > > > Jen- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi All,
>
> > Thank you so much for your help!
> > I was hoping though that I could have a multiple selection though ...
> > So that all my instances of "Jen" found in the first column would
> > select all the ranges in column H:K, instead of just the first
> > instance found?
> > You see I have difficulties to even properly describe what i
> > "want".
> > Hopefully this is possible?!
>
> > Thanks again, Jen
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Hi Dave,

That's exactly what it should do! A charm!

There is 1 more thing though ....

What I wanted to do is:
1. I copy a cell with a number eg. 5
2. then run the macro to select my cells
3. Paste Special> Operation Multiply on the cells your macro selected.

But it seems that when I run the macro... my copied value does not
retain in teh memory...?
Would it be possible to keep that?
(or could the macro at the end of the selection process prompt me for
a value to multiply it with?)

Hope you can help once again!!
Jen

Are you trying to paste special|add a value in a cell or are you trying to
increment that range by an amount you want to enter--like the way you entered
the name?

I guessed that you wanted to specify the value--not the cell:

Option Explicit
Sub testme()

Dim myName As String
Dim RngToSearch As Range
Dim myFoundRng As Range
Dim FoundCell As Range
Dim FirstAddress As String
Dim DummyCell As Range
Dim QtyToAdd As Double

myName = InputBox(Prompt:="Enter a name")
If myName = "" Then
Exit Sub
End If

QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1)
If QtyToAdd = 0 Then
Exit Sub
End If

With ActiveSheet
Set RngToSearch = .Range("a:a")
Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

FirstAddress = ""
Set myFoundRng = Nothing
With RngToSearch
Set FoundCell = .Cells.Find(what:=myName, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox myName & " wasn't found!"
Else
FirstAddress = FoundCell.Address
Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K
Do
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Set myFoundRng = Union(myFoundRng, _
FoundCell.Offset(0, 7).Resize(1, 4))
Loop
With DummyCell
.Value = QtyToAdd
.Copy
End With
myFoundRng.PasteSpecial Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationAdd
DummyCell.ClearContents
'no need to select unless you really want
'myFoundRng.Select
End If
End With

End Sub



Jen wrote:
>
<<snipped>>
> Hi Dave,
>
> That's exactly what it should do! A charm!
>
> There is 1 more thing though ....
>
> What I wanted to do is:
> 1. I copy a cell with a number eg. 5
> 2. then run the macro to select my cells
> 3. Paste Special> Operation Multiply on the cells your macro selected.
>
> But it seems that when I run the macro... my copied value does not
> retain in teh memory...?
> Would it be possible to keep that?
> (or could the macro at the end of the selection process prompt me for
> a value to multiply it with?)
>
> Hope you can help once again!!
> Jen

--

Dave Peterson

On Jun 1, 5:30 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Are you trying to paste special|add a value in a cell or are you trying to
> increment that range by an amount you want to enter--like the way you entered
> the name?
>
> I guessed that you wanted to specify the value--not the cell:
>
> Option Explicit
> Sub testme()
>
> Dim myName As String
> Dim RngToSearch As Range
> Dim myFoundRng As Range
> Dim FoundCell As Range
> Dim FirstAddress As String
> Dim DummyCell As Range
> Dim QtyToAdd As Double
>
> myName = InputBox(Prompt:="Enter a name")
> If myName = "" Then
> Exit Sub
> End If
>
> QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1)
> If QtyToAdd = 0 Then
> Exit Sub
> End If
>
> With ActiveSheet
> Set RngToSearch = .Range("a:a")
> Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
> End With
>
> FirstAddress = ""
> Set myFoundRng = Nothing
> With RngToSearch
> Set FoundCell = .Cells.Find(what:=myName, _
> after:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> lookat:=xlWhole, _
> searchorder:=xlByRows, _
> searchdirection:=xlNext, _
> MatchCase:=False)
>
> If FoundCell Is Nothing Then
> MsgBox myName & " wasn't found!"
> Else
> FirstAddress = FoundCell.Address
> Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K
> Do
> Set FoundCell = .FindNext(after:=FoundCell)
> If FoundCell.Address = FirstAddress Then
> Exit Do
> End If
> Set myFoundRng = Union(myFoundRng, _
> FoundCell.Offset(0, 7).Resize(1, 4))
> Loop
> With DummyCell
> .Value = QtyToAdd
> .Copy
> End With
> myFoundRng.PasteSpecial Paste:=xlPasteValues, _
> operation:=xlPasteSpecialOperationAdd
> DummyCell.ClearContents
> 'no need to select unless you really want
> 'myFoundRng.Select
> End If
> End With
>
> End Sub
>
>
>
>
>
>
>
> Jen wrote:
>
> <<snipped>>
> > Hi Dave,
>
> > That's exactly what it should do! A charm!
>
> > There is 1 more thing though ....
>
> > What I wanted to do is:
> > 1. I copy a cell with a number eg. 5
> > 2. then run the macro to select my cells
> > 3. Paste Special> Operation Multiply on the cells your macro selected.
>
> > But it seems that when I run the macro... my copied value does not
> > retain in teh memory...?
> > Would it be possible to keep that?
> > (or could the macro at the end of the selection process prompt me for
> > a value to multiply it with?)
>
> > Hope you can help once again!!
> > Jen
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Fantastic Dave,
You're my hero!
You've made my weekend!

Cheers, sooooo )))))))