Use next number from list on another sheet 1

[patme]

Worksheet1 contains a list of numbers, 101-199, in cells a1 through a99.
Worksheet2 looks like this:
A1 101
A2 102
A3 103
Worksheet3 looks like this:
A1 104
A2 105
A3 106
How can I make Worksheet2 look like this?:
B1 107
B2 108
|
B14 120
C1 109
C2 110
|
C10 118

Basically, I want to fill in any cell I want with the next available, unused number from my list of numbers...via a macro?

I know, I'm using up my alloted threads for the year, and it's only February! Sorry and TIA.

 

[SkipVought]

Hi,

Do you really need the list of numbers?

Sheet2!B1 is Counta(Sheet2!$1:$1)+Counta(Sheet3!$1:$1) more than Sheet2!A1
[tt]
Sheet2!B1: =Sheet2!A1+Counta(Sheet2!$1:$1)+Counta(Sheet3!$1:$1)
[/tt]


Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[patme]

Well, yeah, I'd like to use the list because my cells aren't really in order. It's more like:
Sheet2
A1 101
A3 102
A5 103
Sheet3
A1 104
A2 105
A3 106
Sheet2
B1 should equal 107

I know, gotta make it confusing don't I!??!

 

[SkipVought]

I got my rows and columns mixed...

but as far as a macro goes, isn't it one mor than the maximum already entered?

Sub OneMore()
    ActiveCell.Value = Application.Max(Sheet2.Cells, Sheet3.Cells) + 1
End Sub

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[patme]

Okay, that worked, once I figured out the sheet syntax. Sorry, not a programmer, but I'm going to have to learn it after this project! Yeah, that works...

2 questions this week, 2 answers, BOTH by Skip! Thanks.

 

[patme]

Is there a way to search ALL sheets in a workbook, w/o having to manually enter them into Skip's VB?

Thanks.

 

[SkipVought]

Sub OneMore()
  dim ws as worksheet, rng as range
  for each ws in worksheets
    if ws.name<>activesheet.name then
      set rng = application.union(rng, ws.cells)
    end if
  next
  ActiveCell.Value = Application.Max(rng) + 1
End Sub

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[patme]

Runtime error '5'. I knew you could come up with something Skip, now I just have to figure out what the error is. I tried removing all the Add-ins per another doc for this error, and no dice.

 

[SkipVought]

On what statement?

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[SkipVought]

Sub OneMore()
  Dim ws As Worksheet, rng As Range
  For Each ws In Worksheets
    If ws.Name <> ActiveSheet.Name Then
      If rng Is Nothing Then
        Set rng = ws.Cells
      Else
        Set rng = Application.Union(rng, ws.Cells)
      End If
    End If
  Next
  ActiveCell.Value = Application.Max(rng) + 1
End Sub

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[patme]

Thanks, I'll see if I can get this one to work.

 

[SkipVought]

Try this as a UDF...

Function OneMore()
  Dim ws As Worksheet
  For Each ws In Worksheets
    If ws.Name <> ActiveSheet.Name Then
        OneMore = OneMore + Application.Max(ws.Cells)
    End If
  Next
  OneMore = OneMore + 1
End Function

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[patme]

Skip,

While this looks cool, I guess I don't know how to use a UDF. I've entered it in a cell as a formula ( =OneMore ), but nothing. I really need to learn VBA (the Very Basic Answers!)

Thanks.

 

[SkipVought]

[tt]
=OneMore()
[/tt]


However, your code must be in a MODULE and not in a Sheet or Workbook Object code sheet.

Is your cell formatted GENERAL?

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[patme]

Formatted as General.

It is in "VBA Project (Filename.xls)", module3. It was NOT in it's own module. I created a new module4 under this same project and still doesn't work.

I don't know how to create a module that is NOT under the "VBA Project (filename.xls)" list.

Also, is there something that I should be putting in the () after the OneMore...?

Thanks.

 

[SkipVought]

It does not need to be in a separate module. Just in a module.

Try this...

Menu item Insert/Function -- select User defined in category and OneMore in name.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[patme]

#Value!

It's like it's expecting an argument or something.

 

[SkipVought]

are you using this one?

Function OneMore()
  Dim ws As Worksheet
  For Each ws In Worksheets
    If ws.Name <> ActiveSheet.Name Then
        OneMore = OneMore + Application.Max(ws.Cells)
    End If
  Next
  OneMore = OneMore + 1
End Function

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[SkipVought]

I think that I may have lost sight of the original objective: to return the NEXT highest number...

Function OneMore()
  Dim ws As Worksheet
  Application.Volatile
  For Each ws In Worksheets
    If ws.Name <> ActiveSheet.Name Then
        If OneMore < Application.Max(ws.Cells) Then OneMore = Application.Max(ws.Cells)
    End If
  Next
  OneMore = OneMore + 1
End Function

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}