Please know that I work diligently to find answers to questions prior to submitting!
I'm having trouble with using variables to move a column to the right. I've debugged with no luck.
Here is the entire routine and below I'll ask specific question.
Sub AddData()
' Excel VBA/Macro Course - TeachExcel.com
'The following are previously defined named ranges in Excel
'
Dim sectionFor As Range
Dim sectionTo As Range
Dim headerRow As Range
Dim subtotalRow As Range
Dim NewDescrip As Range
Dim NewQty As Range
Dim NewPrice As Range
Dim RngInsertRow As Range
Dim RngClear As Range
Dim NewTotal As Range
Set sectionFor = Range("D9:D11")
Set sectionTo = Range("B9:B11")
Set headerRow = Range("B13:E13")
Set subtotalRow = Range("D21")
Set totalcell = Range("E14")
'Add a new row below the header row and copy formatting (except this does not copy borders)
Set RngInsertRow = headerRow.Offset(1, 0)
'Add a new row below the header row and copy formatting
RngInsertRow.EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
'Since a new line was added, need to put the RngInsertRow back up one line
Set RngInsertRow = RngInsertRow.Offset(-1, 0)
' Copy the border and other formatting.
RngInsertRow.Offset(1, 0).Copy
RngInsertRow.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'===============================================
'Set up Ranges for Inputting Invoice data
'===============================================
Set NewDescrip = RngInsertRow.Cells(1, 1)
'MsgBox "NewDescrip address= " & NewDescrip.Address
Set NewQty = NewDescrip.Offset(0, 1)
'MsgBox "NewQty address= " & NewQty.Address
Set NewPrice = NewDescrip.Offset(0, 2)
'Copy the formula from the Total column to the new row. This is now two rows below the totalCell
Set NewTotal = NewDescrip.Offset(0, 3)
'Must use R1C1 references because when formula is copied, it is based on the
' relative position of the cells. It automatically adjusts.
NewTotal.FormulaR1C1 = totalcell.FormulaR1C1
'=====================================================
'Ask User to Input new data
'=====================================================
'Uncomment this later
' Prompt the user to enter a description and directly assign it to the cell
'NewDescrip.Value = InputBox("Enter Description of product")
'NewQty.Value = InputBox("Enter Quantity of product")
'NewPrice.Value = InputBox("Enter Price of product")
NewDescrip.Value = "Stuff I Want"
NewQty.Value = 99
NewPrice.Value = 55
'======================================================
'clear the old data
'=======================================================
' Determine the last used row in the worksheet
Dim lastRow As Long
Dim lastCol As Long
lastRow = Cells(Rows.Count, NewDescrip.Column).End(xlUp).Row
lastCol = Cells(RngInsertRow.Row, Columns.Count).End(xlToLeft).Column
MsgBox "RngInsertRow Address = " & RngInsertRow.Address
MsgBox "lastRow = " & lastRow & "lasCol= " & lastCol
Debug.Print "lastRow: " & lastRow
Debug.Print "lastCol: " & lastCol
'Need to move one column to the left to exclude Total column
lastCol = lastCol - 1
MsgBox "lastCol revised" & lastCol
' Set RngClear to start from one cell below RngInsertRow and extend to the last used row and the second to last column
MsgBox "RngInsertRow " & RngInsertRow.Address
Debug.Print "lastCol: " & lastCol
Set RngClear = Range(RngInsertRow.Offset(1, 0), Cells(lastRow, lastCol))
MsgBox "RngClear= " & RngClear.Cells(1, 1).Address & RngClear.Address
' Debug statement to verify RngClear interpretation
Debug.Print "RngClear: " & RngClear.Address
Debug.Print "lastRow: " & lastRow
Debug.Print "lastCol: " & lastCol
' Display the information in a MsgBox (for verification purposes)
If Not RngClear Is Nothing Then
MsgBox "RngClear starts at " & RngClear.Cells(1, 1).Address & " and includes region " & RngClear.Address
Else
MsgBox "RngClear is not properly defined."
End If
' clear contents of adjusted RngClear
' RngClear.ClearContents
End Sub
What I am having trouble with is in the section I labeled as "clear the old data". I set up a variables named lastRow and lastCol to store row and column numbers. I use a previously defined range to store values into lastRow and lastCol.
Then I need to move a column in a range one to the right relative to the value in lastCol so I subtract one from the lastCol variable. I verified that the lastCol value changes, but when I use lastCol in a range, it seems like the variable's value is not being interpreted correctly.
It goes like this:
I first find the last column of a pre-defined range and assign it to lastCol
as follows
Dim lastRow As Long
Dim lastCol As Long
lastRow = Cells(Rows.Count, NewDescrip.Column).End(xlUp).Row
lastCol = Cells(RngInsertRow.Row, Columns.Count).End(xlToLeft).Column
MsgBox "RngInsertRow Address = " & RngInsertRow.Address
MsgBox "lastRow = " & lastRow & "lasCol= " & lastCol
Debug.Print "lastRow: " & lastRow
Debug.Print "lastCol: " & lastCol
At this point the value of lastCol is 5,
I then subtract one from lastCol and use it to define a new range named RngClear
lastCol = lastCol - 1
MsgBox "lastCol revised" & lastCol
' Set RngClear to start from one cell below RngInsertRow and extend to the last used row and the second to last column
MsgBox "RngInsertRow " & RngInsertRow.Address
Debug.Print "lastCol: " & lastCol
Set RngClear = Range(RngInsertRow.Offset(1, 0), Cells(lastRow, lastCol))
MsgBox "RngClear= " & RngClear.Cells(1, 1).Address & RngClear.Address
' Debug statement to verify RngClear interpretation
Debug.Print "RngClear: " & RngClear.Address
Debug.Print "lastRow: " & lastRow
Debug.Print "lastCol: " & lastCol
' Display the information in a MsgBox (for verification purposes)
If Not RngClear Is Nothing Then
MsgBox "RngClear starts at " & RngClear.Cells(1, 1).Address & " and includes region " & RngClear.Address
Else
MsgBox "RngClear is not properly defined."
End If
' clear contents of adjusted RngClear
' RngClear.ClearContents
End Sub
I verify several times in the routine that after subtracting 1, the value of lastCol is 4, but this somehow does not get passed through to the following line of code
Set RngClear = Range(RngInsertRow.Offset(1, 0), Cells(lastRow, lastCol))
I expect RngClear address to be $B$15:$D$17 but rather VBA result is $B$15:$E$17.
Why? What is going on with the lastCol variable? Is there something wrong with the Set RngClear = line of code?
Thank you as always.
I'm attaching the whole .xlsm file