For Next Loop...Copying from a cell one column to another...

[bourbon84]

Hi all. I am brand new to VBA, and am fumbling my way thru this. Any help
is appreciated. I have one worksheet with several thousand rows, and about
10 columns. I need to loop thru all the rows in column 5 to see if each cell
meets a certain criteria (is the text there = "Person" or "Place" or
"Thing"?)

If R2, C5 = "Person", then copy "Person" to R2, C4.
If R2, C5 = "Place", then copy whatever is in R2, C6 to R2, C4
If R2, C5 = "Thing", then copy whatever is in R2, C7 to R2, C4

You'd think, with all the Excel help pages and example code out there, that
I could figure this out. But apparently, I can't.

[Otto Moehrbach]

This little macro should do what you want. HTH Otto
Sub LoopCol5()
Dim RngCol5 As Range
Dim i As Range
Dim c As Long
Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
For Each i In RngCol5
Select Case i.Value
Case "Person": c = 5
Case "Place": c = 6
Case "Thing": c = 7
Case Else: GoTo NextCell
End Select
Cells(i.Row, 4).Value = Cells(i.Row, c).Value
NextCell:
Next i
End Sub

"bourbon84" <[email protected]> wrote in message
news:[email protected]...
> Hi all. I am brand new to VBA, and am fumbling my way thru this. Any
> help
> is appreciated. I have one worksheet with several thousand rows, and
> about
> 10 columns. I need to loop thru all the rows in column 5 to see if each
> cell
> meets a certain criteria (is the text there = "Person" or "Place" or
> "Thing"?)
>
> If R2, C5 = "Person", then copy "Person" to R2, C4.
> If R2, C5 = "Place", then copy whatever is in R2, C6 to R2, C4
> If R2, C5 = "Thing", then copy whatever is in R2, C7 to R2, C4
>
> You'd think, with all the Excel help pages and example code out there,
> that
> I could figure this out. But apparently, I can't.

[Dave Peterson]

I like to stay away from GoTo's.

An alternative based on Otto's code:

Sub LoopCol5()
Dim RngCol5 As Range
Dim i As Range
Dim c As Long
Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
For Each i In RngCol5
Select Case i.Value
Case "Person": c = 5
Case "Place": c = 6
Case "Thing": c = 7
Case Else: c = -9999
End Select
if c > -9999 then
Cells(i.Row, 4).Value = Cells(i.Row, c).Value
end if
Next i
End Sub

Otto Moehrbach wrote:
>
> This little macro should do what you want. HTH Otto
> Sub LoopCol5()
> Dim RngCol5 As Range
> Dim i As Range
> Dim c As Long
> Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
> For Each i In RngCol5
> Select Case i.Value
> Case "Person": c = 5
> Case "Place": c = 6
> Case "Thing": c = 7
> Case Else: GoTo NextCell
> End Select
> Cells(i.Row, 4).Value = Cells(i.Row, c).Value
> NextCell:
> Next i
> End Sub
>
> "bourbon84" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all. I am brand new to VBA, and am fumbling my way thru this. Any
> > help
> > is appreciated. I have one worksheet with several thousand rows, and
> > about
> > 10 columns. I need to loop thru all the rows in column 5 to see if each
> > cell
> > meets a certain criteria (is the text there = "Person" or "Place" or
> > "Thing"?)
> >
> > If R2, C5 = "Person", then copy "Person" to R2, C4.
> > If R2, C5 = "Place", then copy whatever is in R2, C6 to R2, C4
> > If R2, C5 = "Thing", then copy whatever is in R2, C7 to R2, C4
> >
> > You'd think, with all the Excel help pages and example code out there,
> > that
> > I could figure this out. But apparently, I can't.

--

Dave Peterson

[bourbon84]

Gentlemen, thanks for the rapid replies. I will try both of those
suggestions and post whether or not they did it for me. Either way, it's a
step in the right direction, and helpful insight into how to work with VBA.
Thanks.
Mark

"Dave Peterson" wrote:

> I like to stay away from GoTo's.
>
> An alternative based on Otto's code:
>
> Sub LoopCol5()
> Dim RngCol5 As Range
> Dim i As Range
> Dim c As Long
> Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
> For Each i In RngCol5
> Select Case i.Value
> Case "Person": c = 5
> Case "Place": c = 6
> Case "Thing": c = 7
> Case Else: c = -9999
> End Select
> if c > -9999 then
> Cells(i.Row, 4).Value = Cells(i.Row, c).Value
> end if
> Next i
> End Sub
>
> Otto Moehrbach wrote:
> >
> > This little macro should do what you want. HTH Otto
> > Sub LoopCol5()
> > Dim RngCol5 As Range
> > Dim i As Range
> > Dim c As Long
> > Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
> > For Each i In RngCol5
> > Select Case i.Value
> > Case "Person": c = 5
> > Case "Place": c = 6
> > Case "Thing": c = 7
> > Case Else: GoTo NextCell
> > End Select
> > Cells(i.Row, 4).Value = Cells(i.Row, c).Value
> > NextCell:
> > Next i
> > End Sub
> >
> > "bourbon84" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi all. I am brand new to VBA, and am fumbling my way thru this. Any
> > > help
> > > is appreciated. I have one worksheet with several thousand rows, and
> > > about
> > > 10 columns. I need to loop thru all the rows in column 5 to see if each
> > > cell
> > > meets a certain criteria (is the text there = "Person" or "Place" or
> > > "Thing"?)
> > >
> > > If R2, C5 = "Person", then copy "Person" to R2, C4.
> > > If R2, C5 = "Place", then copy whatever is in R2, C6 to R2, C4
> > > If R2, C5 = "Thing", then copy whatever is in R2, C7 to R2, C4
> > >
> > > You'd think, with all the Excel help pages and example code out there,
> > > that
> > > I could figure this out. But apparently, I can't.
>
> --
>
> Dave Peterson
>

[bourbon84]

Worked like a charm. You guys rock.
Mark

"bourbon84" wrote:

> Gentlemen, thanks for the rapid replies. I will try both of those
> suggestions and post whether or not they did it for me. Either way, it's a
> step in the right direction, and helpful insight into how to work with VBA.
> Thanks.
> Mark
>
> "Dave Peterson" wrote:
>
> > I like to stay away from GoTo's.
> >
> > An alternative based on Otto's code:
> >
> > Sub LoopCol5()
> > Dim RngCol5 As Range
> > Dim i As Range
> > Dim c As Long
> > Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
> > For Each i In RngCol5
> > Select Case i.Value
> > Case "Person": c = 5
> > Case "Place": c = 6
> > Case "Thing": c = 7
> > Case Else: c = -9999
> > End Select
> > if c > -9999 then
> > Cells(i.Row, 4).Value = Cells(i.Row, c).Value
> > end if
> > Next i
> > End Sub
> >
> > Otto Moehrbach wrote:
> > >
> > > This little macro should do what you want. HTH Otto
> > > Sub LoopCol5()
> > > Dim RngCol5 As Range
> > > Dim i As Range
> > > Dim c As Long
> > > Set RngCol5 = Range("E2", Range("E" & Rows.Count).End(xlUp))
> > > For Each i In RngCol5
> > > Select Case i.Value
> > > Case "Person": c = 5
> > > Case "Place": c = 6
> > > Case "Thing": c = 7
> > > Case Else: GoTo NextCell
> > > End Select
> > > Cells(i.Row, 4).Value = Cells(i.Row, c).Value
> > > NextCell:
> > > Next i
> > > End Sub
> > >
> > > "bourbon84" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi all. I am brand new to VBA, and am fumbling my way thru this. Any
> > > > help
> > > > is appreciated. I have one worksheet with several thousand rows, and
> > > > about
> > > > 10 columns. I need to loop thru all the rows in column 5 to see if each
> > > > cell
> > > > meets a certain criteria (is the text there = "Person" or "Place" or
> > > > "Thing"?)
> > > >
> > > > If R2, C5 = "Person", then copy "Person" to R2, C4.
> > > > If R2, C5 = "Place", then copy whatever is in R2, C6 to R2, C4
> > > > If R2, C5 = "Thing", then copy whatever is in R2, C7 to R2, C4
> > > >
> > > > You'd think, with all the Excel help pages and example code out there,
> > > > that
> > > > I could figure this out. But apparently, I can't.
> >
> > --
> >
> > Dave Peterson
> >