If Cell in row has data then do this: HELP

  • Thread starter Thread starter Yogi_Bear_79
  • Start date Start date
Y

Yogi_Bear_79

If column I has data in it I want Excel to perform the following.

1. Concatanate the data in the coresponding row column G to the end of the
data in the coresponding row in column F

2. Move the data from column I to the coresponding cell in the same row in
column G
 
Slipping and sliding....

Sub ChutesAndLadders()
Dim shtCurrent As Worksheet
Set shtCurrent = ActiveSheet
Dim rngColumnI As Range
Set rngColumnI = shtCurrent.Range("I1", "I65000")
Dim intTotalRows As Integer
intTotalRows = shtCurrent.UsedRange.Rows.Count
Dim rngRow As Range
For Each rngRow In rngColumnI
If rngRow.Row > intTotalRows Then Exit For
If Not IsEmpty(rngRow.Value) Then
With rngRow
.Offset(0, -3).Value = .Offset(0, -
3).Value & .Offset(0, -2).Value
.Offset(0, -2).Value = rngRow.Value
.Value = ""
End With
End If
Next
End Sub

If I understood you right, that will do it!

-Brad
 
Dim rng as Range, cell as Range
set rng = Range(Cells(1,"I"),Cells(rows.count,"I").End(xlup)
for each cell in rng
if not isempty(cell) then
cells(rng.row,"F").Value = cells(rng.row,"F").Value _
& cels(rng.row,"G").Value
cells(rng.row,"G").Value = cell
cell.Clearcontents
end if
Next

This would replace a formula if it is in one of the cells being manipulated,
so the assumption is that the cells do not contain formulas.
 
Code doesn't seemto be working;
At first I thought it was working, then I noticed all it was doing was
clearing the contents of the cells in column I

When I comment out these two lines nothing happens at all:

cells(rng.row,"G").Value = cell
cell.Clearcontents

Now I fI understood the code corrctly, editing out those two lines should
still allow F&G to concatanate. Which they are currently not
 
Actually,

I've found that it does work sort of:
It concatanates only F1&G1. Funny thing is it does it for as many cells in
column I that do have data so as an example:

F1 = Hello
G1 = World

Column I has 4 cells that contain data
nowF1 = Hello World World World World
 
Back
Top