Search un-opened file for next available number

[fishbone7373]

Hi I am new to here:
Please go easy on me.

I have a sequential set of numbers in Column A:

For example

Column A Column B
Test-1 Bob
Test-2 Tom
Test-3
Test-4


What is the code or function I need to select the first empty cell location in column B but return the value for the locatio in column A.

For example: Test-3

Would be the next available number, and is what I want to be put into the cell.


Thank you for the help
Sincerely

 

[SkipVought]

hi,
Welcome to Tek-Tips.

Please be precise with your language, because words have specific meanings. Test-3 is NOT a 'number.'

Also, what kind of file are you opening? What method are you using to open the file?

In addition, what application are your coding in?

And finally, what code do you have so far? Please post your code an explain where you might be having problems with your code or logic.

Please address EACH of these issues clearly, concisely and completely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fishbone7373]

Hi Skip:
Thank you for fast reply.

The files are all Excel.

I do not have any code, and I am not sure if code is needed or if a function can do it.

I have a Excel file named Test Log which has a list of sequential test report numbers.
For example: AM-11-001, AM-11-002, etc.

Normally we go to the log sheet to get the next test number.

Then we open another Excel file named Test Report then we assign the next number from the Log File to cell on the Test Report.

My goal is from the Test Report File in a cell is to be able to automatically obtain the next number available from the Log Sheet.

There is more I would like to do but this is my main focus right now that I can not figure out.

I was thinking that a simple =find+1 function may work, but I have not been successful.

Thank you

 

[SkipVought]

On a separate sheet you can add a QueryTable to get data from your workbook in question. faq68-5829

Assuming that your sheet name is Sheet1 and the headings in those 2 columns are TestNum & Testee['/b], then the SQL codes is...

SELECT Min(`Sheet1$`.TestNum) AS 'TstNum'
FROM `Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Testee Is Null

this returns the next test number.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fishbone7373]

Hi Skip:

Thanks for the update.

I tried to use the MS Query but I am sorry I am confused.

The test log is in table form with 1 header on top.

But when I try to use the MS Queary it say not in table form.

I don't understand the data is a table form.

Can you advise what I am doing wrong for using the Queary?

Thank you

 

[UnsolvedCoding]

There are several things you could do.

You could build an array and then use the last number in the array as a way to ID which cell to select. Example -

cells(i, "B").value = cells(i,"A").value

You could do a statment to get the last row of information and then use that as a reference. Example -

' Get the last row
ilastrow = Sheets("Sheet Name").Range("B1").End(xlDown).Row

Range("B" & ilastrow).value = Range("A" & ilastrow).value


If you want to go a slower way thats easier to understand try something like this -

' Assumes you use a header row, if not start with i = 1

i=2

Do

' If there is only one claim account for it.
If Trim(Range("B2").Value) = "" Then Exit Do

' This sets it so that if you go down a list an empty cells exits the loop
If Trim(Cells(i, "B").Value) = "" Then Exit Do

' If a value is present increase counter by one
If Cells(i, "B").Value <> "" Then i = i + 1

Loop

Then use the value of i to indicate what cell to use. Something like this -

cells(i,"B").value = cells(i,"A").value

FYI - the difference between Range("A" & i).value and cells(i,"A").value is basically the typing and if you are going to use multiple cells.

 

[SkipVought]

But when I try to use the MS Queary it say not in table form.

What sheet is your TABLE on?

What sheet is your QUERY on?

Please post (COPY and PASTE) the first 3 rows of information on your TABLE sheet, starting with SHEET ROW 1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fishbone7373]

Hi Skip:

The "table I have is simple"
but excel does not see it a table?

Test Number Part Number Name
AM-11-001 17C582-000[] Jim Neuron
AM-11-002 625392-0000 Jim Neuron
AM-11-003 625392-0000 Jim Neuron
AM-11-004 253218-5212 Jim Neuron
AM-11-005 75C812-0004 Jim Neuron
AM-11-006 75C553-0003 Jim Neuron
AM-11-007 75C553-0003 Jim Neuron
AM-11-008 1-17F665-000* Jim Neuron
AM-11-009 75C751-0000 Jim Neuron
AM-11-010 75C691-715x Jim Neuron
AM-11-011 75C553-0003 Amy Spirnak
AM-11-012 75c774 Amy Spirnak
AM-11-013 75C977 Amy Spirnak
AM-11-014 75C977, Amy Spirnak
AM-11-015 75C977, Amy Spirnak
AM-11-016 75C977, Amy Spirnak
AM-11-017 75C977, Amy Spirnak
AM-11-018 75C813-000* Amy Spirnak
AM-11-019 75C571 Amy Spirnak
AM-11-020 75C691-715x Amy Spirnak
AM-11-021 75C977, Jason Jones
AM-11-022 75C977, Jason Jones
AM-11-023 75F264-000AB Jason Jones


I am using 2003 and can not Format it as a table.

Thanks

 

[fishbone7373]

Hi Unsolved coding,

thanks for you suggestion.
I was looking at trying to use a function with a macro but don't have enough knowledge to know where to even start.

Looking at the above items I provided, I want to be able to open the file then look for the next test number which is empty.

I aplogize for my ignorance.

Please help me understand the best way to simply open up the file named "test log" and select the next available number that does not have anything in Part Number or Name Column.

I don't know if a quary is easier or a Macro, either is ok for me.

The Quary is interesting and first time I have tried it in excel.

But the Macro was my first thought which I tried by recording but could not get it to duplicate etc.

Thanks Again.

 

[SkipVought]

So Test Number is in A1 on your sheet?

When you get to the Query By Example Grid and have the Add Tables window open, hit the Options button and check all items

Now you should see your Sheets (tables)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fishbone7373]

Hi Skip:

Thank you now I got the Query to work.
I can see my tables inside of the sheet.

My confusion right now is that if I have a new sheet open and Cell F2 is to contain the next available test number which from the list above would be AM-11-024.

How can I use the Queary to get only that value where the B column or Part number is blank?

Can I use what you wrote above?
SELECT Min(`Sheet1$`.TestNum) AS 'TstNum'
FROM `Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Testee Is Null

If so where does that get put in?

thanks again

 

[SkipVought]

You put this query on a new separate sheet. The result will ALWAYS be returned to the same place on your sheet: probably A2 on that sheet, let's say Sheet3.

So in F2 you could have the reference...
[tt]
F2: =Sheet3!A2
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fishbone7373]

Hi Skip:

I understand the reference part and adding the sheet.

Current I can get the Queary and all data onto the new sheet on as shown above.

After that, I am confused, do I need to use a macro now?
Or how do I input the items you wrote?

To keep things simple, the file name is test log and I have added a sheet named hidden that has the table above.

From there how do I choose the next test number available?

Then how do I put that into A2 on the sheet or another cell where I can reference from.

Again, I apoogize and relize you are probably pulling your hair out at my ignorance, but I thank you for your help.

 

[SkipVought]

Current I can get the Queary and all data onto the new sheet on as shown above.

Exactly what does that statment mean? I would expect that the only data returned by the query, would be ONE TEST NUMBER in A2--the NEXT test number!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fishbone7373]

Hi Skip:

Using the Import Data, Excel Queary I am able to get the table to appear on a new sheet in the file.

The table I am referring to is the table above with 3 columns.

The full table appears.

I am not sure from there what I should do.

The first test number does appear in A2 as you mentioned.

But the full table appears.

What is the best method to put the next Test Number available based on if column B is null?

Thanks

Ryan

 

[fishbone7373]

I finally got it, thank you skip

 

[SkipVought]

For the benefit of all browsers, please explain WHAT you did in order to get the desired result.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}