VBA: Moving Cell Value from one column to adjacent cell if destination is blank

[willinbrief]

Hey guys,
This is my first post so please bare that in mind.

I am working with a software that outputs a ton of data and I need to move data from "G1:G300" to "I1:I300" but only in the cases where the "I" column's cells are blank and have no value.

This is what I have so far:

Sub ChangeCellz()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Range(I1:I2000)
For Each Rng In WorkRng
If Rng.Value = 0 Then
Rng.Value = xCell.offset(0,-3).Value
End If
Next
End Sub

Thanks for any help

-William Salisbury

 

[Joe4]

Welcome to the Board!

Not sure what "xCell" is. Since you have not set it to anything, you cannot Offset from it.

Try this:

Sub ChangeCellz()

    Dim Rng As Range
    Dim WorkRng As Range

    Application.ScreenUpdating = False

    Set WorkRng = Range("I1:I300")
    For Each Rng In WorkRng
        If Rng.Value = 0 Then
            Rng.Value = Rng.Offset(0, -2).Value
[COLOR=#ff0000]            Rng.Offset(0, -2).ClearContents[/COLOR]
        End If
    Next Rng
    
    Application.ScreenUpdating = True
    
End Sub

Not sure if you want to remove the value from column G if you copy it to column I. If not, then remove the line in red above.

 

[willinbrief]

Thanks Joe4,
I will give this a try next time I can!

 

[willinbrief]

Joe4,
It worked!! Now I just have to make some code that will reclass the G column's values to text and I will be completely done. Appreciate your help this was such a hurdle for me and you helped knock it down!!

_Will

 

[Joe4]

I think removing the ".Value" parts of each one should fix that, i.e.

Sub ChangeCellz()

    Dim Rng As Range
    Dim WorkRng As Range

    Application.ScreenUpdating = False

    Set WorkRng = Range("I1:I300")
    For Each Rng In WorkRng
        If Rng.Value = 0 Then
            [COLOR=#ff0000]Rng = Rng.Offset(0, -2)[/COLOR]
            Rng.Offset(0, -2).ClearContents
        End If
    Next Rng
    
    Application.ScreenUpdating = True
    
End Sub

 

[willinbrief]

Joe4,
That seems to help, but I am seeing a weird issue that even though the code works independently when I follow up
with a collection of commands meant to restructure the excel sheet the values that are moved initially by our code
does not show up when the following code is used to move it's column around.




In this case, Colum B was the original Column I that which was addressed in earlier code. The "I" Column value all move over except the individual cells we shifted previously.

ActiveSheet.Unprotect
Sheets("Spec Data").Range("A:E,G:H,J:AI").EntireColumn.Delete
Columns("B").Cut
Columns("A").Insert shift:=x1ToRight
Columns("A").ColumnWidth = 13
Columns("B").ColumnWidth = 100
Range("A1:B1").Select
With Selection.Font
.Size = 12
End With
Range("B2").CurrentRegion.Sort _
Key1:=Range("B2"), _
Order1:=xlAscending, _
Header:=True
Range("A1").EntireRow.Font.Bold = True
Dim iRow As Integer, iCol As Integer
Dim oRng As Range
Set oRng = Range("B1")
iRow = oRng.Row
iCol = oRng.Column
Do
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown
iRow = iRow + 2
Else
iRow = iRow + 1
End If
Loop While Not Cells(iRow, iCol).Text = ""

Dim x As Long, lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value = "-10000in" Or Cells(x, 1) = "Undefined Size" Or Cells(x, 2) = "NA" Or Cells(x, 2) = "" Then
Rows(x).Delete
End If
Next x
End Sub

 

[Joe4]

The best thing to do in these cases where you have lot of different things going on is to step through your code line-by-line, and watch what happens.
Many time, the issue will become evident, when you see what is happening.

If you have two monitors, put your VB Editor code window on one, and your workbook on the other. Otherwise, split screen so you can see both.
Then step the the code one line at a time using the F8 and watch what happens with each step.