Update - I figured it out!
Original message:
Hey,
I copied some vba code from elsewhere and I've been trying to modify it to fit my needs, but it's doesn't work as expected.
When I step into the process I can see that it recognizes LastRow as being row 63, but then nothing else happens.
Before the code below is executed, part of the data is copied and pasted as values off to the side as a kind of historical data, and it can be longer than Column A appears.
The idea is for the program to check all cells from A2, down to the "last row" of Column A, and if it is empty, then make the coresponding cells in column H,I, and J empty. If not, then put one of three formulas in the cell.
However, after evaluating the information it doesn't input the formulas. I'm was under the assumption that my 'cell' variable is not an integer, so I tried creating a "cellNum" variable to use instead with no success.
Ok, I'm done rambling - any ideas?
Edit - After playing around for a bit I was able to get it to populate with formulas, although they aren't doing what I wanted... I fixed the code below to show where I am at.
The contents of I2, for example are "=TEXT(B12/26/2013&C12/26/2013,"000000000000000")"
Every instance is referencing the 12th row? Not to mention that it's taking the value out of B12, which is the date 12/26/2013, when I wanted it to concat the two cells to the right of it.
Note Finished code that works:
Original message:
Hey,
I copied some vba code from elsewhere and I've been trying to modify it to fit my needs, but it's doesn't work as expected.
When I step into the process I can see that it recognizes LastRow as being row 63, but then nothing else happens.
Before the code below is executed, part of the data is copied and pasted as values off to the side as a kind of historical data, and it can be longer than Column A appears.
The idea is for the program to check all cells from A2, down to the "last row" of Column A, and if it is empty, then make the coresponding cells in column H,I, and J empty. If not, then put one of three formulas in the cell.
However, after evaluating the information it doesn't input the formulas. I'm was under the assumption that my 'cell' variable is not an integer, so I tried creating a "cellNum" variable to use instead with no success.
Ok, I'm done rambling - any ideas?
Edit - After playing around for a bit I was able to get it to populate with formulas, although they aren't doing what I wanted... I fixed the code below to show where I am at.
The contents of I2, for example are "=TEXT(B12/26/2013&C12/26/2013,"000000000000000")"
Every instance is referencing the 12th row? Not to mention that it's taking the value out of B12, which is the date 12/26/2013, when I wanted it to concat the two cells to the right of it.
Note Finished code that works:
Code:
' First, refresh the data connection to pull the new data
ActiveWorkbook.RefreshAll
' The following bit of code checks column A, and for every cell that is not empty it puts formulas in columns H-J,
' and those that are empty, it clears out.
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
Dim cell As Range
Dim rChange As Range
Set rChange = Range("A2:A" & LastRow)
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each cell In rChange
If cell.Row > LastRow Then Exit For
If cell = "" Then
cell.Offset(0, 7).Clear
cell.Offset(0, 8).Clear
cell.Offset(0, 9).Clear
Else
cell.Offset(0, 7).Formula = "=IF(COUNTIF(K:K,I" & cell.Row & ")=0,""New"",""not"")"
cell.Offset(0, 8).Formula = "=TEXT(B" & cell & "&C" & cell.Row & ",""000000000000000"")"
cell.Offset(0, 9).Formula = "=IF(COUNTIF(I:I,K" & cell.Row & ")=0,""Gone"",""notg"")"
End If
Next
End If
ExitHandler:
Set cell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
Last edited:
