Copy data from 1 row, 2 separate columns based on criteria and paste into 2 rows and turn 1 value negative, repeat for new date

bluepinky

New Member
Joined
Dec 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Good evening everyone.
I'm new here, so please assume I know nothing about VBA. I haven't used it in about 6 years am very rusty and I am having a really big trouble with this problem. Here is my data set before the copy and paste

1606937918942.png


I need a macro that will work its way through the dates to the end. Copying data from column H & I if its >1 and pasting it into column B. However, if for one specific date, like the 1Oct19 there is data in both columns H and I then I would like that pasted into two rows in column B. If the data is only in one column then only one date row is required for the data to be pasted in.

Another issue is that all data that is copied from Column H must be turned into a negative, Ie. the 23 will become -23 and so on, so that I can use the SUM function to get a running total. (no VBA required here)

Therefore, at the end of the exercise, I hope to have this (The red formatting in Columns B and C, represent negative values)

1606938350570.png


I hope that is a clear enough explanation of my issue. I really do hope for some help as I have been working on this all day and haven't got anywhere.
Thank you in advance for your assistance

Bluepinky
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Hello Bluepinky,
try this code...
VBA Code:
Sub TurnToNegativeRows()

     Dim varWS As Worksheet
     Dim varNRows As Long
     Dim varRange1 As Range, varRange2 As Range
     Dim varLastRow As Long
     
     Set varWS = Worksheets("YourSheetName")
     varNRows = varWS.Range("G" & Rows.Count).End(xlUp).Row
     Set varRange2 = varWS.Range("G2:G" & varNRows)
     varLastRow = 2
     
     For Each varRange1 In varRange2
        If varRange1.Offset(0, 1).Value > 1 Then
            varWS.Range("B" & varLastRow) = "-" & varRange1.Offset(0, 1).Value
            varWS.Range("B" & varLastRow).Offset(0, -1) = varRange1.Value
            varLastRow = varLastRow + 1
        End If
        If varRange1.Offset(0, 2).Value > 1 Then
            varWS.Range("B" & varLastRow) = varRange1.Offset(0, 2).Value
            varWS.Range("B" & varLastRow).Offset(0, -1) = varRange1.Value
            varLastRow = varLastRow + 1
        End If
     Next
     
End Sub
 
Solution

bluepinky

New Member
Joined
Dec 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you Excel Max. You are a genius!!

Worked first time :biggrin::biggrin::biggrin: and saved my sanity
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Thanks, for your reaction.
I'm glad that you have got "Bingo" from first try.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,283
Messages
5,635,299
Members
416,851
Latest member
zeldadav

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
Top