Increment value

[HVtech]

Hi,

I got a sheet with dynamic range, and stumble into a view questions.
When I add a new row (by userform) I would like to increment the value of the row above(which is also the last populated)

emptyRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
Cells(emptyRow, 1).Value = ???

How is this done?

Then a tricky one:
In column C I got this formula:

=IF(B3<>"";HYPERLINK("#(N3)";"view notes");"")

How can I put this formula and still poiting to the proper (same row) cell?
Tried recording a macro but that doesn't work, Just copies the formula without adjusting it to the correct cell on same row.

Help is greatly appreciated!




Office 2010

 

[SkipVought]

hi.

dim lRow as long

'add new row to table

with ws.usedrange
   lRow = .row + .rows.count 
   cells(lRow, "C").formula = "=IF(B" & lRow & "<>"";HYPERLINK("#(N" & lRow & ")";"view notes");"")"
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

ACTUALLY...

if you make this table a 'Table', that is a Structured Table, (selecting IN the table and Insert > Tables > Table), ALL your formulas will propogate to new rows, when you enter any literal in the new row.

NO VBA REQUIRED!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Hi Skip,
Thanks for your answer.
Let me show you more of my code, and how I tried to implement your input:

Sheets("digid").Activate

Dim emptyRow As Long
Dim ws As Worksheet
Set ws = Worksheets("digid")

'find first empty row in database
emptyRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'Export Data to worksheet
'Cells(emptyRow, 1).Value = LastRow(1)
Cells(emptyRow, 3).formula = "=IF(B" & lRow & "<>"";HYPERLINK("#(N" & lRow & ")";"view notes");"")"
Cells(emptyRow, 4).Value = Txtsofi.Value
Cells(emptyRow, 5).Value = TxtVoorl.Value
Cells(emptyRow, 6).Value = Txtnaam.Value

Getting n error on the # now.
Or should the code be different?

Office 2010

 

[SkipVought]

if you want to use YOUR CODE for locating the next row, then you have to use YOUR VARIABLE in the formula for the next row.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Ok, tried your code like this:

Private Sub OpslaanButton_Click()

'Make Sheet digid Active
Sheets("digid").Activate

Dim emptyRow As Long
Dim ws As Worksheet
Set ws = Worksheets("digid")
Dim lRow As Long

'find first empty row in database
emptyRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws.UsedRange
 lRow = .Row + .Rows.Count
   cells(lRow, "C").formula = "=IF(B" & lRow & "<>"";HYPERLINK("#(N" & lRow & ")";"view notes");"")"
End With

'Export Data to worksheet
'Cells(emptyRow, 1).Value = LastRow(1)
Cells(emptyRow, 4).Value = Txtsofi.Value
Cells(emptyRow, 5).Value = TxtVoorl.Value
Cells(emptyRow, 6).Value = Txtnaam.Value
Cells(emptyRow, 7).Value = Txtadres.Value
Cells(emptyRow, 8).Value = Txtnr.Value
Cells(emptyRow, 9).Value = TxtPostcode.Value
Cells(emptyRow, 10).Value = Txtwoonpl.Value
Cells(emptyRow, 11).Value = Txtdigid.Value
Cells(emptyRow, 12).Value = Txtdigiww.Value
Cells(emptyRow, 14).Value = TxtOpm.Value

Call NieuweKlant_Initialize

End Sub

But still gives an syntax error.


Office 2010

 

[SkipVought]

YOU DID NOT CHANGE THE ROW VARIABLE!!!

You are STILL referring to lRow, which you have not assigned. If you wish, you could assign lRow to YOUR emptyRow variable!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Skip,
Look in the last code I posted, there is your code as you posted it.

Dim lRow As Long

'find first empty row in database
emptyRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws.UsedRange
 lRow = .Row + .Rows.Count
   cells(lRow, "C").formula = "=IF(B" & lRow & "<>"";HYPERLINK("#(N" & lRow & ")";"view notes");"")"
End With

But whatever variable I put in the formula, I get syntax errors.


Office 2010

 

[SkipVought]

sorry...

       .Cells(lRow, "C").Formula = "=IF(B" & lRow & "<>"""";HYPERLINK(""#(N" & lRow & ")"";""view notes"");"""")"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Sorry.. but now I get error 1004 object error
(Told this was a tricky one)

Office 2010

 

[SkipVought]

ON what statement?

BTW it RUNS on my PC!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Error points to:

.Cells(lRow, "C").Formula = "=IF(B" & lRow & "<>"""";HYPERLINK(""#(N" & lRow & ")"";""view notes"");"""")"

Office 2010

 

[SkipVought]

When I run this code on my PC, in TEXAS, I must CHANGE the SEMICOLONS in the formula to COMMAS.

Do you use SEMICOLON for argument separators in formulas or COMMAS?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Recorded this macro:

ActiveCell.FormulaR1C1 = "=IF(RC[1]<>"""",HYPERLINK(RC[13],""check""),"""")"

Does that help?

Office 2010

 

[HVtech]

Changed the semicolon's to comma's.
Now the code no longer gives error, nor hyperlink in C...
pff
Are getting crazy already? I am..
Do appreciate your help very much though!

Office 2010

 

[SkipVought]

did you try substituting into the code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

Yep..

.Cells(lRow, "C").Formula = "=IF(B" & lRow & "<>"""",HYPERLINK(""#(N" & lRow & ")"",""view notes""),"""")"

Office 2010

 

[SkipVought]

WHERE? You did not make the substitution!!!

BTW, did you try the Structured Table thing? You would not even have to WORRY about this formula in VBA, as long as the formula is in EVERY ROW of the table preceeding the new row!!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I take back the Structure Table statements BECAUSE, you have a LITERAL in the HYPPERLINK() formula that refers to the current row.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HVtech]

O yes I did!
This is the code with semicolon's which gives error:

.Cells(lRow, "C").Formula = "=IF(B" & lRow & "<>"""";HYPERLINK(""#(N" & lRow & ")"";""view notes"");"""")"

This the substituded code > semicolon replaced by comma's:

.Cells(lRow, "C").Formula = "=IF(B" & lRow & "<>"""",HYPERLINK(""#(N" & lRow & ")"",""view notes""),"""")"

No, did not try this Structured table thing. because I'm almost there with the sheet, this is almost the last challenge to overcome.
I've never worked with structured tables, so have no clue how or what to

Office 2010