hardcoding cell values based target cells

[drfumbles]

Hey,
I've been coming to this forum for help, so I finally made an account to ask some questions.

I'm writing a vba that will change a column in a set of tables to the values based on the last row of data for each table. The number of tables isn't constant and is generated by different macro.

So far I have:

Private Sub Hardcode_Tables(ByVal Target As Range)

If Intersect(Target, Range("G:G")) Is Nothing Then 'I don't think "G:G" is allowed
Exit Sub
else

'=============pseudo code ============
'if first column = "last string name (this value is only appears once for each table)" & cell to 2 columns to the right is a numeric nonzero value
'copy from top of continuous data down to where logical is true.
'paste values in the same spot
'advance search starting point to stop looking at cells that have been changed to values

end sub

Thanks

 

[drfumbles]

I'm getting closer

Private Sub Hardcode_Columns()
Const csLockText As String = "Flush"
Dim rCell As Range
Dim rLookIn As Range


With Worksheets("Stage Fluid")
Set rLookIn = Intersect(.Range("E:E"), .UsedRange)
If Not rLookIn Is Nothing Then
For Each rCell In rLookIn
With rCell
If (.Text = csLockText & isumeric(rCell.Offset(0, 2).Value)) Then .Value = csLockText
End With
Next rCell
End If
End With
End Sub


I'm not doing the numeric test properly. The code won't compile. "Sub or function not defined"

 

[Michael M]

Check the spelling of "ISNUMERIC"

 

[drfumbles]

I corrected the spelling. The code compiles but still won't change the target cell. If I remove the "& isnumeric" part then the code will change the target cell.

 

[Michael M]

Maybe this....but it dosn't seem logical to me

Private Sub Hardcode_Columns()
Const csLockText As String = "Flush"
Dim rCell As Range, rLookIn As Range


With Worksheets("Stage Fluid")
Set rLookIn = Intersect(.Range("E:E"), .UsedRange)
For Each rCell In rLookIn
If rCell.Value = csLockText And IsNumeric(rCell.Offset(0, 2).Value) Then rCell.Value = csLockText
Next rCell
End With
End Sub

 

[drfumbles]

I changed the AND and it works. I have to find a way to make it change a the column and rows in the table with references to hardcode now off of the same logical.
Thanks for help

 

[drfumbles]

I would love an edit button to change all my typos.

 

[Michael M]

Care to give more detail on this !!!

I have to find a way to make it change a the column and rows in the table with references to hardcode now off of the same logical.

 

[drfumbles]

I need to hard code the table at large. The current row and 135 rows above and 4 columns wide. These can not be hardcoded line by line but change after the logical statement is true.

 

[Michael M]

A simple explanation would be better....I can't see your worksheet OR what you are doing with it !!!

I need to hard code the table at large. The current row and 135 rows above and 4 columns wide. These can not be hardcoded line by line but change after the logical statement is true.