Capture the last Reference no.

[anthonymedz]

Hi Guys,

Good day.
Can someone help me on my little project.
I have plenty series of Reference number that need to update every day and i would like to capture the last reference number just to remind me that that is the last ref number.
For example:
[TABLE="width: 176"]
<tbody>[TR]
[TD]FMGP-2019-5672[/TD]
[/TR]
[TR]
[TD]FMGP-2019-5673[/TD]
[/TR]
[TR]
[TD]FMGP-2019-5674
.
.
.
.
.
.[/TD]
[/TR]
[TR]
[TD]FMGP-2019-5681[/TD]
[/TR]
[TR]
[TD]*Last reference number will be FMGP-2019-5675[/TD]
[/TR]
</tbody>[/TABLE]


Thank you.
Anton

 

[anthonymedz]

Re: How to capture the last Reference no.

I tried to help because your original question was simple for me.
For example if one cell in that range is empty is that the last row with data
Or maybe three different cells in that range are empty what cell is the last one.



This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

Your formula works fine and perfect. I think this thread is close.
Thanks for your help i really appreciate it.
God bless to you man.. Cheers!!

 

[My Aswer Is This]

Re: How to capture the last Reference no.

Glad we were able to help you.

Not sure if you used Macro or formula but either way glad you have a solution to your needs.


Come back here to Mr. Excel next time you need additional assistance.

Your formula works fine and perfect. I think this thread is close.
Thanks for your help i really appreciate it.
God bless to you man.. Cheers!!

 

[anthonymedz]

Re: How to capture the last Reference no.

Here's my finished product. I used macro.
Thank you very much!

'
' fmgp ref # Macro
'


Range("B20").Select
Sheets("Completed").Select
Range("D3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[6]C[-3]"
Range("D3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C17").Select

Sheets("Completed").Select
Range("C4").Select
Sheets("Completed").Select
Range("D4").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(2,1/(R[10]C[-3]:R[49]C[-3]<>""""),R[10]C[-3]:R[49]C[-3])"
Range("D4").Select
Selection.Copy
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=LOOKUP(2,1/(R[52]C[-3]:R[91]C[-3]<>""""),R[52]C[-3]:R[91]C[-3])"
Range("D5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=LOOKUP(2,1/(R[94]C[-3]:R[133]C[-3]<>""""),R[94]C[-3]:R[133]C[-3])"
Range("D6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("C9").Select

 

[My Aswer Is This]

Re: How to capture the last Reference no.

Wow. That's a lot of code.

I do not understand what it's trying to do but if it works for you that's great.

 

[footoo]

Re: How to capture the last Reference no.

Try replacing the code per post # 13 with :

Sheets("Completed").Select
[D3] = [A6].Value
On Error Resume Next
[D4] = [A14:A53].Find("*", [A14], , , , xlPrevious)
[D5] = [A57:A96].Find("*", [A57], , , , xlPrevious)
[D6] = [A100:A139].Find("*", [A100], , , , xlPrevious)
On Error GoTo 0
[D9].ClearContents
[C9].Select

 

[anthonymedz]

Re: How to capture the last Reference no.

Try replacing the code per post # 13 with :

Sheets("Completed").Select
[D3] = [A6].Value
On Error Resume Next
[D4] = [A14:A53].Find("*", [A14], , , , xlPrevious)
[D5] = [A57:A96].Find("*", [A57], , , , xlPrevious)
[D6] = [A100:A139].Find("*", [A100], , , , xlPrevious)
On Error GoTo 0
[D9].ClearContents
[C9].Select

Hi,
It works! that's look easier. Thanks for helping me! cheers!