For example, in the following code, I look up a value from a table, increment the value, the write the new value back to the table. Right now, the value is written back to the table based on the cell name (i.e. "b5"). This all falls apart if the user sorts the table or adds/deletes and rows above it. What I would like to do is determine the name of the cell that the value was loaded from at the time of the lookup so that the new value can be put back in the same place.
'*** Lookup last DEWR number from Properties Page ***
intDEWRNum = Application.WorksheetFunction.VLookup( _
"Current DEWR", Worksheets("Properties").ListObjects("propertiesTable").Range, 2, False)
If intDEWRNum < 1 Then
intDEWRNum = 1 ' If DEWR field is blank then this is the first DEWR.
Else
intDEWRNum = intDEWRNum + 1 ' Increment DEWR field
End If
Worksheets("Properties").Range("b5").Value = intDEWRNum
'^ This is what I want to avoid.***********
newSheet.Name = "DEWR " & Str(intDEWRNum) ' Name new worksheet with new DEWR number
ActiveSheet.Range("j6").Value = Str(intDEWRNum) ' Insert current DEWR number into sheet
Dim f as Range
Set f = Worksheets("Properties").ListObjects("propertiesTable"). _
Range.Columns(1).Find("Current DEWR",lookin:=xlvalues, lookat:=xlwhole)
If not f is nothing then
intDEWRNum = f.offset(0,1).Value
If intDEWRNum < 1 Then
intDEWRNum = 1 ' If DEWR field is blank then this is the first DEWR.
Else
intDEWRNum = intDEWRNum + 1 ' Increment DEWR field
End If
f.offset(0,1).Value= intDEWRNum
'etc....
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments