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,129
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
233
Peter_SSs
Peter_SSs
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
891
Gajendran Yadhav
G
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
444
maiwarits
M
M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
83
MrsFraser07
M
S
  • Solved
VBA merge cells in another column based on already merged cells. Possible?
Replies
2
Views
710
SwanB
S

Forum statistics

Threads
1,224,830
Messages
6,181,228
Members
453,025
Latest member
Hannah_Pham93

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