PDA

View Full Version : [SOLVED] Convert negative to positive numbers in range being copied



DeanP
01-23-2019, 05:39 AM
I'm using the code below to copy values from one sheet to another. However, some of the values being copied are negative.
If a value is negative I need to change it into a positive and copy the value for that cell only into a different column ("I")
I'm not sure how to change the code to do this. I've tried ElseIf with Abs and ElseIf with - but in both cases, the values
posted are not correct.
Here is the code:

With ThisWorkbook.Worksheets("Volume Allocation")
LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Offset(, -1).Column
x = 7

For i = 5 To LastCol
If .Cells(37, i).Value > 0 Then
Sheets("Journal").Cells(x, "J").Value = .Cells(7, i).Value
ElseIf .Cells(37, i).Value < 0 Then
Sheets("Journal").Cells(x, "I").Value = -(.Cells(7, i).Value)
x = x + 18
End If
Next i
End With
Any advice would be appreciated.

Paul_Hossler
01-23-2019, 07:29 AM
Did you want


.Cells(7, i).Value


to be row 37

in both places?

DeanP
01-23-2019, 11:01 AM
Hi Paul,
Having thought about your question, no I don't need row 37 at all. In the original code I had this

If .Cells(7, i).Value <> 0 Then which copied all the values in column I in "Journal" except that the negative
values should be one column over to the left (but in the same row) and should be made positive. That's what I was trying to achieve
when I change the code as I posted previously.

Paul_Hossler
01-23-2019, 02:08 PM
Maybe a sample workbook would help, showing before and after

大灰狼1976
01-23-2019, 07:54 PM
Private Sub CommandButton1_Click()
With ThisWorkbook.Worksheets("Volume Allocation")
LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Offset(, -1).Column
x = 7

For i = 5 To LastCol
If .Cells(37, i).Value > 0 Then
Sheets("Journal").Cells(x, "J").Value = .Cells(7, i).Value
ElseIf .Cells(37, i).Value < 0 Then
Sheets("Journal").Cells(x, "I").Value = -(.Cells(7, i).Value)
End If
x = x + 18
Next i
End With
End Sub

DeanP
01-23-2019, 10:47 PM
Maybe a sample workbook would help, showing before and after



Sample workbook attached.
With code below:

With ThisWorkbook.Worksheets("Volume Allocation")
LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Offset(, -1).Column
x = 7

For i = 5 To LastCol
If .Cells(7, i).Value <> 0 Then
Sheets("Journal").Cells(x, "J").Value = .Cells(7, i).Value
x = x + 18
End If
Next i
End With

Paul_Hossler
01-24-2019, 08:39 AM
If I just change your macro to handle the negative values:





Option Explicit

Sub AsIs()
Dim LastCol As Long, i As Long, x As Long

With ThisWorkbook.Worksheets("Volume Allocation")
LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Offset(, -1).Column
x = 7

For i = 5 To LastCol
If .Cells(7, i).Value > 0 Then
Sheets("Journal").Cells(x, "J").Value = .Cells(7, i).Value
ElseIf .Cells(7, i).Value < 0 Then
Sheets("Journal").Cells(x, "I").Value = -.Cells(7, i).Value
End If
x = x + 18
Next i
End With
End Sub





this is what I get down to row 79

23617

Doesn't look close to the data in your example 'This is what I want'

Do you want the output rows spread out like that in col I and J which is what a tweak to your macro produces, or packed like in your 'what I want'?


If you do want it packed try this version



Option Explicit

Sub PackedData()
Dim LastCol As Long, i As Long, x As Long

With ThisWorkbook.Worksheets("Volume Allocation")
LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Column
x = 7

For i = 5 To LastCol
If .Cells(7, i).Value > 0 Then
Sheets("Journal").Cells(x, "J").Value = .Cells(7, i).Value
ElseIf .Cells(7, i).Value < 0 Then
Sheets("Journal").Cells(x, "I").Value = -.Cells(7, i).Value
End If
x = x + 1
Next i
End With
End Sub

DeanP
01-24-2019, 10:09 AM
Thanks for this Paul.
The spreadsheet I sent before was to give an idea of where the negative values should go in relation to columns etc. When
I run your suggested code, I get a different result. The negative value -290.27 is placed in the correct column, but not
on the correct row. It is being placed in row 79. I know why that is, but I don't know how to correct it.
I've sent an updated version of my data. To explain briefly: in "Volume Allocation" I have 17 values in B9:B25, and values
in row 8 starting at col E.
The values in col B each represent a cost line that has to be posted by journal into a ledger. The values assigned to these
lines can be either dr or cr depending on what has been calculated by another process. The value in row 8 represents the
balancing account.
For every unique value in row 8, I need, in my journal template (which is what this macro is creating), a line for that value
plus 17 lines below it for the cost lines. I already have the code that copies the calue in row 8 1 + 17 times and copies the
cost codes in the "Journal" sheet. It does this everytime the value in row 8 changes, except if the value in row 7 is zero.
What's happening now, is that the zero value columns (F & G) are not being ignored when I run the code, therefore the value
in row I7 is being placed (18 x 3) fows down from row 26 in the jnls folder.
I'm not sure how I correct this, as I copied the code you suggested exactly.

Paul_Hossler
01-24-2019, 11:29 AM
that description and the latest example doesn't seem anything like the initial ones

My code was based on the previous code just tweaked for negative numbers so I'm not surprised it didn't work for you

Let's try this version

I'm not sure about your attached Journal (renamed to Journal-Original) worksheet - were the cells I marked in red correct?

The output of this macro is on a new Journal (so that I wouldn't mess with your Original)






Option Explicit
Const colAcct As Long = 4
Const colLoc As Long = 5
Const colDebit As Long = 9
Const colCredit As Long = 10

Sub Post()
Dim wsJournal As Worksheet, wsVolume As Worksheet
Dim rVolume As Range
Dim rowVolume As Long, colVolume As Long, rowJournal As Long
Dim rowHeader As Long, rowTotal As Long, rowStart As Long



'init
Set wsVolume = Worksheets("Volume Allocation")
Set wsJournal = Worksheets("Journal")
Set rVolume = wsVolume.Cells(8, 5).CurrentRegion
rowTotal = rVolume.Rows(1).Row
rowHeader = rVolume.Rows(2).Row
rowStart = rVolume.Rows(3).Row
rowJournal = 7

With wsVolume
'accross columns
For colVolume = 5 To rVolume.Columns.Count
Select Case .Cells(rowTotal, colVolume).Value
Case Is = 0
GoTo NextCol
Case Is < 0
wsJournal.Cells(rowJournal, colCredit).Value = Round(-.Cells(rowTotal, colVolume).Value, 2)
Case Is > 0
wsJournal.Cells(rowJournal, colDebit).Value = Round(.Cells(rowTotal, colVolume).Value, 2)
End Select

wsJournal.Cells(rowJournal, colAcct).Value = .Cells(rowHeader, colVolume).Value

rowJournal = rowJournal + 1

For rowVolume = rowStart To rVolume.Cells(1, 1).Row + rVolume.Rows.Count - 1
wsJournal.Cells(rowJournal, colAcct).Value = .Cells(rowHeader, colVolume).Value
wsJournal.Cells(rowJournal, colLoc).Value = .Cells(rowVolume, 2).Value

Select Case .Cells(rowVolume, colVolume).Value
Case Is = 0
GoTo NextRow
Case Is < 0
wsJournal.Cells(rowJournal, colCredit).Value = Round(-.Cells(rowVolume, colVolume).Value, 2)
Case Is > 0
wsJournal.Cells(rowJournal, colDebit).Value = Round(.Cells(rowVolume, colVolume).Value, 2)
End Select

rowJournal = rowJournal + 1

NextRow:
Next rowVolume
NextCol:
Next colVolume

End With
End Sub

DeanP
01-25-2019, 07:27 AM
Hi Paul
Thanks for this. The final result should be as The Journal-Original sheet (ignore the values in col K, they should not be there)
So all the values in row 7 of "Volume Allocation" should be in the credit column if they are debits here, or in the debit
column if they are credits here.
Everything else is correct.

Paul_Hossler
01-25-2019, 12:40 PM
Try this version

BTW - in your original your had 3200, but the line items were in 3110

Any change to the macro for this?

What about the columns with costs in row 6, but no line items?

DeanP
01-25-2019, 01:07 PM
Hi Paul,

3200 was a typo 3110 is correct. The data in Volume Allocation covers columns up to Col BJ, so I just gave a sample of what most of the columns look like. So basically ignore the fact that row 6 had no line items, in the actual data it does.

DeanP
01-25-2019, 11:54 PM
Hi Paul,
Again thank you so much for all your help. I can't get this macro to work, it does everything except what you suggested. I'm
sure it's something that I'm doing but I just cannot see it.
I've taken your code and combined with with the rest of the code that makes up the entire macro. Posted below
Besides the Volume Allocation sheet, I have 2 other sheets for which I will have to change some of the range references
but the same steps that I havenoted will be executed (At the moment the entire macro only works with the Volume Allocation
sheet).
You wull see from my Dim statement that I have made reference to the other 2 sheets, and I have changed some of the references
to reflect what sheet is being referred to (e.g. Where you had "rowHeader" I've amended to "VolRowHeader").
Nothing happens in the "Journal" sheet in the debit and credit columns when the macro is run.
Here is the code:

Option Explicit
Const colAcct As Long = 4
Const colLoc As Long = 5
Const colDebit As Long = 9
Const colCredit As Long = 10
Sub FuncAllocJnl()

Dim wsJournal As Worksheet, wsAPercent As Worksheet
Dim wsVolume As Worksheet, wsAssess As Worksheet, wsSPS As Worksheet
Dim LastCol As Long, x As Long, i As Long, n As Long, q As Long
Dim LastRow As Long, rowJournal As Long
Dim rng As Range, c As Range
Dim rVolume As Range, rAssess As Range, rSPS As Range
Dim colVolume As Long, colAssess As Long, colSPS As Long
Dim VolRowTotal As Long, AssRowTotal As Long, SPSRowTotal As Long
Dim RowVolume As Long, RowAssess As Long, RowSPS As Long
Dim VolRowHeader As Long, AssRowHeader As Long, SPSRowHeader As Long
Dim VolRowStart As Long, AssRowStart As Long, SPSRowStart As Long
Set wsJournal = Worksheets("Journal")
Set wsAPercent = Worksheets("Allocation %")
Set wsVolume = Worksheets("Volume Allocation")
Set wsAssess = Worksheets("Assess Allocation")
Set wsSPS = Worksheets("SPS Allocation")
'Preparing the sheet by removing previous month data and resetting journal header for current month
Application.ScreenUpdating = False

With wsJournal
.Rows("7:" & Rows.Count).ClearContents
.Rows("7:" & Rows.Count).ClearFormats
.Cells(4, 3).Value = Date
.Cells(4, 5) = Format(Date, "yyyy")
.Cells(4, 6).NumberFormat = "mm"
.Cells(4, 8).Value = "Functional Allocations" & " " & MonthName(Month(Date)) & " " & Year(Date)
End With

'FOR VOLUME ALLOCATIONS
'Copy all location codes for which allocation % is not zero
With wsAPercent
Set rng = ThisWorkbook.Worksheets("Allocation %").Range("B6:D" & ThisWorkbook.Worksheets("Allocation %").[B65536].End(3).Offset(-1, -1).Row)
rng.AutoFilter 3, ">0", xlAnd, "<>"
Application.Index(rng.Offset(1), , 1).Copy ThisWorkbook.Worksheets("Journal").[E8]
rng.AutoFilter
End With

'Copy all BS codes for which allocation % is not zero
With wsAPercent
Set rng = ThisWorkbook.Worksheets("Allocation %").Range("B6:D" & ThisWorkbook.Worksheets("Allocation %").[B65536].End(3).Offset(-1, -1).Row)
rng.AutoFilter 3, ">0", xlAnd, "<>"
Application.Index(rng.Offset(1), , 2).Copy ThisWorkbook.Worksheets("Journal").[F8]
rng.AutoFilter
End With
'Correct formatting of rows copied in 2 steps above
With wsJournal
Range("E8:F" & Cells(Rows.Count, "F").End(xlUp).Row).Select
With Selection
.ClearFormats
.HorizontalAlignment = xlLeft
.Font.Name = "Arial"
.Font.Size = 9
End With
End With
'Copy all the nominal codes that have to be posted, transpose the layout, duplicate for the number of location codes needed
With wsVolume

LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Offset(, -1).Column
x = 7
For i = 5 To LastCol
If .Cells(7, i).Value <> 0 Then
Sheets("Journal").Cells(x, "D").Value = .Cells(8, i).Value
x = x + Sheets("Journal").Cells(2, 16).Value
End If
Next i
End With
'Duplicate nominals to be posted for every location code line and insert location 050 at first line
With wsJournal
.Cells(7, 5).Value = "'50"
.Cells(7, 6).Value = "'015"

For Each c In Range("D7:D" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(2)
c.Offset(1).Resize(17).Value = c.Value
Next c
End With

'Copy all the VOLUME debits and credit, with correct transposition for journal
Set rVolume = wsVolume.Cells(8, 5).CurrentRegion
VolRowTotal = rVolume.Rows(1).Row
VolRowHeader = rVolume.Rows(2).Row
VolRowStart = rVolume.Rows(3).Row
rowJournal = 1

With wsVolume
For colVolume = 5 To rVolume.Columns.Count
Select Case .Cells(VolRowTotal, colVolume).Value
Case Is = 0
GoTo NextCol
Case Is > 0
wsJournal.Cells(rowJournal, colCredit).Value = Round(.Cells(VolRowTotal, colVolume).Value, 2)
Case Is < 0
wsJournal.Cells(rowJournal, colDebit).Value = Round(-.Cells(VolRowTotal, colVolume).Value, 2)
End Select

wsJournal.Cells(rowJournal, colAcct).Value = .Cells(VolRowHeader, colVolume).Value

rowJournal = rowJournal + 1

For RowVolume = VolRowStart To rVolume.Cells(1, 1).Row + rVolume.Rows.Count - 1
wsJournal.Cells(rowJournal, colAcct).Value = .Cells(VolRowHeader, colVolume).Value
wsJournal.Cells(rowJournal, colLoc).Value = .Cells(RowVolume, 2).Value
Select Case .Cells(RowVolume, colVolume).Value
Case Is = 0
GoTo NextRow
Case Is < 0
wsJournal.Cells(rowJournal, colCredit).Value = Round(-.Cells(RowVolume, colVolume).Value, 2)
Case Is > 0
wsJournal.Cells(rowJournal, colDebit).Value = Round(.Cells(RowVolume, colVolume).Value, 2)
End Select

rowJournal = rowJournal + 1
NextRow:
Next RowVolume
NextCol:
Next colVolume

End With

'Copy location codes to be posted for all nominal code lines
' With ThisWorkbook.Worksheets("Journal")
' Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row).AutoFill Destination:=Range("E7:E" & Range("D" & Rows.Count).End(xlUp).Row), xlFillValues
' Range("F7:F" & Range("F" & Rows.Count).End(xlUp).Row).AutoFill Destination:=Range("F7:F" & Range("D" & Rows.Count).End(xlUp).Row), xlFillValues
' End With

'Assign values to columns A, B, H & copy down for all rows in journal
With wsJournal
.Cells(7, 1).Value = "Post"
.Cells(7, 2).Value = "1"
.Cells(7, 8).Value = .Cells(4, 8).Value

Range("A7").AutoFill .Range("A7:A" & .Cells(.Rows.Count, "D").End(xlUp).Row), xlFillCopy
Range("B7").AutoFill .Range("B7:B" & .Cells(.Rows.Count, "D").End(xlUp).Row), xlFillCopy
Range("H7").AutoFill .Range("H7:H" & .Cells(.Rows.Count, "D").End(xlUp).Row), xlFillCopy
End With
Application.ScreenUpdating = True
End Sub

Paul_Hossler
01-26-2019, 06:17 AM
Be glad to look, but can you post another workbook that is representivie of what you have now?

DeanP
01-26-2019, 06:40 AM
Hi Paul,
The attached is the actual file im working on (or a copy of it). What you see in the Journal sheet is what I have after running the macro as it is now. The only changes I made today is that I put in some code to shade the current region of the Journal sheet, and as I'm going to be creating a pivot table I started to put some of the variables in the Dim statement.


I'm sure that the whole thing is very untidy and messy, but I'm learning as I go.

Paul_Hossler
01-27-2019, 05:30 PM
Couple of issues

1. The data layout changed from the first example such that .CurrentRegion pulled in more cells than the original example. I deleted one line and added 3



'Copy all the VOLUME debits and credit, with correct transposition for journal
' Set rVolume = wsVolume.Cells(8, 5).CurrentRegion <<<<<<<<<<<<<< no valid any more

Set rVolume = Range(wsVolume.Range("E7"), wsVolume.Range("E7").End(xlDown)) ' E7:E37
Set rVolume = Intersect(rVolume.EntireRow, wsVolume.Columns(1)) ' A7:A37
Set rVolume = rVolume.Resize(rVolume.Rows.Count, rVolume.Cells(2, 1).End(xlToRight).Column - 2) ' A7:BJ37

VolRowTotal = rVolume.Rows(1).Row
VolRowHeader = rVolume.Rows(2).Row
VolRowStart = rVolume.Rows(3).Row
rowJournal = 1



2. When you use With / End With to establish a 'parent' object (like wsJournal), the 'child' objects like Range, Cells, etc. still need their dot



With wsJournal
.Range("A1").Value = 1234
End With



Without the dot, Range("A1") applies to whatever the ActiveSheet is, even though the Range("A1").Value = 1234 is in a With / End With


I think I caught all of them

You've added some more that I didn't see what you wee doing, so you still have some work to do :yes

DeanP
01-28-2019, 06:42 PM
Hi Paul,
Thank you for sending this. I don't understand what the 3 new lines are doing? The end result seems to be A7:BJ37, but it
should be E7:BJ36. I tried to change it but I can't get it right, mainly because I don't understand what these lines
mean or what they are doing:



Set rVolume = Intersect(rVolume.EntireRow, wsVolume.Columns(5))
Set rVolume = rVolume.Resize(rVolume.Rows.Count, rVolume.Cells(2, 1).End(xlToRight).Column - 2)


When I run this macro now rows in the journal are being over written and placed in the wrong columns.

Paul_Hossler
01-28-2019, 08:11 PM
I grabbed too much data on Volume

There were two rowJournal lines that I changed -- marked "PHH"

It might be easier to process Volume sheet by going down each column with data and completely writing your JE line

Easy enough to restructure what we have, except I don't see where some of the values (like %allocation) come into play

DeanP
01-29-2019, 04:40 PM
Thanks for sending this.

This part of the code seems to replace values in the Journal sheet (col E). I now have the value "500" in some of the rows in column E. I should not have 500 in here at all. In the Volume Allocation sheet, the last row before the end total (row 36) belongs to the value 500, but all of them have zero value in the columns, so this row should be excluded. In any event, column E in the Journal sheet should not be changed at all.

Aside from that, it's perfect.

Paul_Hossler
01-29-2019, 04:42 PM
As example, this just uses data on Volume to create the Journal by walking the columns and the rows.

I'm not sure that you need the Allocation sheet since any 0 entries are not copied to Journal




Option Explicit
'column headers in Journal
Const colPost As Long = 1
Const colOne As Long = 2
Const colElement1 As Long = 4
Const colElement2 As Long = 5
Const colElement3 As Long = 6
Const colDescription As Long = 8
Const colDebit As Long = 9
Const colCredit As Long = 10

Sub FuncAllocJnl()
'This macro creates a copy of the journal posting template for the posting of FUNCTIONAL ALLOCATIONS
'Written by Dean Pitout - January 2019
Dim wsJournal As Worksheet, wsAPercent As Worksheet
Dim wsVolume As Worksheet, wsAssess As Worksheet, wsSPS As Worksheet
Dim LastCol As Long, x As Long, i As Long, n As Long, q As Long
Dim LastRow As Long, rowJournal As Long
Dim rng As Range, c As Range, w As Range
Dim rVolume As Range, rAssess As Range, rSPS As Range
Dim colVolume As Long, colAssess As Long, colSPS As Long
Dim rowVolumeTotal As Long, AssRowTotal As Long, SPSRowTotal As Long
Dim RowVolume As Long, RowAssess As Long, RowSPS As Long
Dim VolRowHeader As Long, AssRowHeader As Long, SPSRowHeader As Long
Dim VolRowStart As Long, AssRowStart As Long, SPSRowStart As Long

Set wsJournal = Worksheets("Journal")
' Set wsAPercent = Worksheets("Allocation %")
Set wsVolume = Worksheets("Volume Allocation")
'Set wsAssess = Worksheets("Assess Allocation")
'Set wsSPS = Worksheets("SPS Allocation")
'Preparing the sheet by removing previous month data and resetting journal header for current month
Application.ScreenUpdating = False

With wsJournal
.Rows("7:" & .Rows.Count).ClearContents
.Rows("7:" & .Rows.Count).ClearFormats
.Cells(4, 3).Value = Date
.Cells(4, 5) = Format(Date, "yyyy")
.Cells(4, 6) = Format(Date, "mm")
.Cells(4, 6).NumberFormat = "mm"
.Cells(4, 8).Value = "Functional Allocations" & " " & MonthName(Month(Date)) & " " & Year(Date)
End With


'Copy all the VOLUME debits and credit, with correct transposition for journal
Set rVolume = Range(wsVolume.Range("E7"), wsVolume.Range("E7").End(xlDown).Offset(-1, 0)) ' E7:E36
Set rVolume = Intersect(rVolume.EntireRow, wsVolume.Range("E7").CurrentRegion) ' A7:BL36

rowVolumeTotal = rVolume.Rows(1).Row
VolRowHeader = rVolume.Rows(2).Row
VolRowStart = rVolume.Rows(3).Row
rowJournal = 7

With wsVolume
For colVolume = 5 To rVolume.Columns.Count
Select Case .Cells(rowVolumeTotal, colVolume).Value
Case Is = 0
GoTo NextCol
Case Is > 0
wsJournal.Cells(rowJournal, colCredit).Value = Round(.Cells(rowVolumeTotal, colVolume).Value, 2)
Case Is < 0
wsJournal.Cells(rowJournal, colDebit).Value = Round(-.Cells(rowVolumeTotal, colVolume).Value, 2)
End Select

wsJournal.Cells(rowJournal, colPost).Value = "Post"
wsJournal.Cells(rowJournal, colOne).Value = 1
wsJournal.Cells(rowJournal, colElement1).Value = .Cells(VolRowHeader, colVolume).Value
wsJournal.Cells(rowJournal, colElement2).Value = "'50"
wsJournal.Cells(rowJournal, colElement3).Value = "'15"
wsJournal.Cells(rowJournal, colDescription).Value = wsJournal.Range("H4").Value

rowJournal = rowJournal + 1

For RowVolume = VolRowStart To rVolume.Cells(1, 1).Row + rVolume.Rows.Count - 1
Select Case .Cells(RowVolume, colVolume).Value
Case Is = 0
GoTo NextRow
Case Is < 0
wsJournal.Cells(rowJournal, colCredit).Value = Round(-.Cells(RowVolume, colVolume).Value, 2)
Case Is > 0
wsJournal.Cells(rowJournal, colDebit).Value = Round(.Cells(RowVolume, colVolume).Value, 2)
End Select

wsJournal.Cells(rowJournal, colPost).Value = "Post"
wsJournal.Cells(rowJournal, colOne).Value = 1
wsJournal.Cells(rowJournal, colElement1).Value = .Cells(VolRowHeader, colVolume).Value
wsJournal.Cells(rowJournal, colElement2).Value = "'" & .Cells(RowVolume, 2).Text
wsJournal.Cells(rowJournal, colElement3).Value = "'" & .Cells(RowVolume, 3).Text
wsJournal.Cells(rowJournal, colDescription).Value = wsJournal.Range("H4").Value

rowJournal = rowJournal + 1
NextRow:
Next RowVolume
NextCol:
Next colVolume
End With

With wsJournal
.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
.Columns(1).ColumnWidth = 10
End With

Application.ScreenUpdating = True
End Sub

DeanP
01-29-2019, 05:31 PM
Hi Paul,


Wow - this is amazing! So much better. Thank you!


I don't need the totals on the journal sheet, so from row 706 through 723. Can I adjust the current region by using offset to exclude the totals?

Paul_Hossler
01-30-2019, 07:33 AM
Hi Paul,


Wow - this is amazing! So much better. Thank you!


I don't need the totals on the journal sheet, so from row 706 through 723. Can I adjust the current region by using offset to exclude the totals?

Thanks (blush)

Easier to just adjust the end value of the columns' For/Next loop