Can't increment Alphanumeric string by one

**Ochimus** · 02-06-2016, 08:56 AM

Attached file 160206 Text Autonumber.xlsm opens with Dashboard containing button that opens Order Form spreadsheet.

When the Order Form is called, I want the Macro to find the latest Job Number in the Job Database worksheet (e.g. "Job 5"), increment it by one as the next number in sequence ("Job 6"), enter that in the next row down, and copy it to Cell C3 in the Order Form.

The "click and drag" approach throws a "1004" error on the Selection Autofill line ("Autofill method of Range class failed")

Option Explicit

Sub NextOrder()

Dim b As Long, c As Long

Sheet1.Activate
b = Range("B65536").End(xlUp).Row
c = Range("B65536").End(xlUp).Row + 1

Selection.AutoFill Destination:=Range("B" & b, Range("B" & c)), Type:=xlFillDefault

Sheet2.Select
Range("C3") = Sheet1.Range("B" & c)

End Sub

Any pointers or suggestions received gratefully as ever.

Ochimus

**Akuini** · 02-06-2016, 09:25 AM

Try this:

Sub NextOrder()

Dim b As Long, c As Long
Dim job As String

  Sheet1.Activate

  c = ActiveSheet.Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  job = "Job " & c - 1

  Range("B" & c) = job
  Sheet2.Activate
  Range("C3") = job

End Sub

**Jim885** · 02-06-2016, 10:05 AM

Rather than using the .Select or . Activate function that would slow the code down, and have your screen jump from sheet to sheet, try this;

Sub NextOrder()

Dim lr As Long
Dim c As String

With Sheets("JOB DATABASE")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    c = "Job " & lr
    .Cells(lr + 1, 2) = c
End With

With Sheets("ORDER FORM")
    .Range("C3") = c
End With

End Sub

I'm guessing there will be more to this code, so also include;

Application.ScreenUpdating

as part of your final code.

It would be inserted in this manner;

Sub NextOrder()

Dim lr As Long
Dim c As String

Application.ScreenUpdating = False

With Sheets("JOB DATABASE")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    c = "Job " & lr
    .Cells(lr + 1, 2) = c
End With

With Sheets("ORDER FORM")
    .Range("C3") = c
End With

Application.ScreenUpdating = True

End Sub

**Akuini** · 02-06-2016, 06:20 PM

Originally Posted by Jim885

Rather than using the .Select or . Activate function that would slow the code down, and have your screen jump from sheet to sheet, try this;

Hi, Jim.
Thanks for the advice. I'll keep that in mind.

**Jim885** · 02-07-2016, 12:08 AM

Originally Posted by Akuini

Hi, Jim.
Thanks for the advice. I'll keep that in mind.

Akuini,
Glad it all worked out. You're welcome.

Ochimus,
Thanks for the rep.

**Ochimus** · 02-06-2016, 02:58 PM

Thanks to both for prompt response and solutions.

Ochimus

Can't increment Alphanumeric string by one

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Can't increment Alphanumeric string by one

Re: Can't increment Alphanumeric string by one

Re: Can't increment Alphanumeric string by one

Re: Can't increment Alphanumeric string by one

Re: Can't increment Alphanumeric string by one

Re: Can't increment Alphanumeric string by one

Thread Information

Users Browsing this Thread

Similar Threads

Increment the value of an alphanumeric field by one if that combination already exists

[SOLVED] Alphanumeric Increment based on previous row value

Increment Alphanumeric Value of a Cell on each print job

[SOLVED] Need code to pull numeric data out of an alphanumeric string (string not constant)

Extracting Letter/Number from Alphanumeric string (4 different times from same string)

[SOLVED] Remove numbers from alphanumeric string. No standard format to the string.

Replicate and increment alphanumeric keywords?

Bookmarks

Bookmarks

Posting Permissions