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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
Solution
Thank you Excel Max. You are a genius!!

Worked first time :biggrin::biggrin::biggrin: and saved my sanity
 
Upvote 0
Thanks, for your reaction.
I'm glad that you have got "Bingo" from first try.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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