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]

It worked?

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

 

[HVtech]

No..
Like I said, no more errors, but also no hyperlink in C column.
You get hyperlinks in column C when you run the code??

Maybe I should attach the workbook??

Office 2010

 

[SkipVought]

ii36250

at

bellhelicopter

dot

textron

dot

com

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

 

[HVtech]

on its way..
thanks

Office 2010

 

[SkipVought]

You have some DATA in a distant row. (row 200 and change)

Select the ROW below your table THRU the last row of the sheet.

Right - Click > DELETE.

NOW run your code.

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

 

[HVtech]

OMG.. I'm embaressed..
Well that was the easy part.. LOL
As you can see in the sheet, in column A are numbers hyperlinked to direcories.
How can I increment the number in this hyperlink?


Office 2010

 

[SkipVought]

There are other options.

New me explain how my approch has caused a problem. select C232 and see what is there. This is a result of the UsedRange method. In reality, the BORDERS that you have pre-formatted to row 200+, often mess up an application and cause unexpected things to happen.

As a rule, ONLY enter formulas and formats for ACTUAL DATA in a table. Do not pre-load/format in anticipation of future rows of data. There are unexpected consequences to sheet/table functionality.

Here's what you could also use that would work...

With ws[b][highlight].[A1].CurrentRegion[/highlight][/b]
 lRow = .Row + .Rows.Count
    .Cells(lRow, "C").Formula = "=IF(B" & lRow & "<>"""",HYPERLINK(""#(N" & lRow & ")"",""view notes""),"""")"
  '  .Cells(lRow, "M").Formula = "=IF(B" & lRow & "<>"""";HYPERLINK(""#(A" & lRow & ")"";""terug"");"""")"
End With

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

 

[HVtech]

Ok, will keep that in mind!


Office 2010

 

[SkipVought]

How did you incriment the first 10?

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

 

[HVtech]

Manually...
copied the code to Notepad++, made a macro in Notepad++ and ran that to the orignal 180 (or so) lines, then copied back those lines to excel.
But... now the whole sheet loops corrupted..
Sorting the column gives a 400 error (whatever that may be..)
Are you willing to give a deeper look to the sheet and enhance some code maybe?
This is gonna cost me my hair...

Office 2010

 

[SkipVought]

If it were me, I would be using NO hyperlinks at all.

I would be using the worksheet_SelectionChange event to contextually, depending on the column, open a workbook or jump to column N on the corresponding row.

But you could use the same technique that you are using in column C in column A, structured appropriately.

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

 

[HVtech]

Oke, will see how far I'll get for now.
Probably have to start over with the sorting buttons.

I'm tired, so going offline for now.

Thanks very much for your time and support!!

Office 2010