Need to more value to empty cells but from Column P to G on VBA i use now

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
Hi

The macro below moves data from column M to 1 column over, but now I need to go backwards.

If cells in G are empty then fill the empty cells that have value in Column P.

Not sure its as simple as changing --> Set rngM = Range("G1:G150") and maybe the the rng value change it to -9 or something?


Code: 
Public Sub MoveRangeIfNotBlank() 'Move value to other cell if next cell is empty'
  Dim rngM As Excel.Range
  Dim Rng As Excel.Range
  
  Set rngM = Range("M1:M150")
  
  For Each Rng In rngM.Cells
    If Len(Rng.value) > 0 And Len(Rng.Offset(0, 1).value) = 0 Then
      Rng.Offset(0, 1).value = Rng.value
      Rng.Clear
    End If
  Next Rng
End Sub

Having a problem showing an example hopefully explaining it right


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH][/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



<table width="" class='pxpxpxpxpx"wysiwyg_dashes' style="width: " wysiwyg_cms_table_head"=""><tbody></tbody></table>
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
I think I got the image working...

But you can see it G &H the column H really suppose to be Column P


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td]HOURLY AMOUNT[/td][td]SALARY AMOUNT[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td]
402.16​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td]
3500​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td][/td][td]
1730.77​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td][/td][td]
2538.46​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]
1500​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td][/td][td]
2567.31​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td][/td][td]
2269.25​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td]
1800​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td]
2600​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]
352​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td][/td][td]
2500​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td][/td][td]
1000​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td]
4280​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td]
1080​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td]
960​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td]
3000​
[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 19054424_PYRJRN_Mon_Oct_9_15_55[/td][/tr][/table]
 
Upvote 0
Maybe this :

Public Sub MoveRangeIfNotBlank() 'Move value to other cell if next cell is empty'
Dim rngM As Excel.Range
Dim Rng As Excel.Range

Set rngM = Range("P1:P150")

For Each Rng In rngM.Cells
If Len(Rng.Value) <> 0 And Len(Rng.Offset(0, -9).Value) = 0 Then
Rng.Offset(0, -9).Value = Rng.Value
Rng.Clear
End If
Next Rng
End Sub
 
Upvote 0
This work thank you. I was thinking G:G - While I should of thought it has to be P:P firs then -9 etc. Thanks cheers
 
Upvote 0
HI so I ran into an issue on this. The reason is because the columns change on me all the time.

Instead of going from P to -9

If SALARYAMOUNT header is in row 1. Then move the data in the cells to the column that has Hourly Amount header in row 1. Data range start at 2:150

I want to use the header name as a Gide so it will move the data in the right cells. Rather then moving it by column letter and -9. Any help would be great.

What I'm using now:

Code: 
Public Sub MoveRangeIfNotBlank() 'Move value to other cell if next cell is empty'
  Dim rngM As Excel.Range
  Dim Rng As Excel.Range

  Set rngM = Range("P1:P150")

  For Each Rng In rngM.Cells
    If Len(Rng.Value) <> 0 And Len(Rng.Offset(0, -9).Value) = 0 Then
      Rng.Offset(0, -9).Value = Rng.Value
      Rng.Clear
    End If
  Next Rng
 End Sub
[TABLE="width: 107"]
<colgroup><col></colgroup><tbody>[TR]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
How about
Code: 
Public Sub MoveRangeIfNotBlank()
'Move value to other cell if next cell is empty'
    Dim Scol As Long
    Dim Dcol As Long
    Dim Rng As Range
    Dim Ofst As Long
    
    Scol = Rows(1).Find(What:="[COLOR=#0000ff]Overlap[/COLOR]", After:=Range("A1"), _
        LookAt:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Dcol = Rows(1).Find(What:="[COLOR=#0000ff]Location[/COLOR]", After:=Range("A1"), _
        LookAt:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Ofst = Scol - Dcol
    

    For Each Rng In Range(Cells(2, Dcol), Cells(Rows.Count, Dcol).End(xlUp))
      If Len(Rng.Value) = 0 And Len(Rng.Offset(, Ofst).Value) <> 0 Then
        Rng.Value = Rng.Offset(, Ofst).Value
        Rng.Offset(, Ofst).Clear
      End If
    Next Rng
 End Sub
Change the words in blue to match.
 
Upvote 0
Perfect thanks so much for the help. This helps a lot on what I need to do cheers
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
190
Peter_SSs
Peter_SSs
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
843
Gajendran Yadhav
G
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
400
maiwarits
M
S
  • Solved
VBA merge cells in another column based on already merged cells. Possible?
Replies
2
Views
686
SwanB
S
mehidy1437
  • Question Question
Requesting for experts comments suggestion on my vba code
Replies
5
Views
883
mehidy1437
mehidy1437

Forum statistics

Threads
1,223,912
Messages
6,175,348
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top