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

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.

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

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" wrote in message
...
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.

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

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" wrote in message
...
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

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

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" wrote in message
...
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

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

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" wrote in message
...
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