Find next empty cell in excel worksheet using vba

[chanman525]

Hi Experts,

I have spent a few day browsing this forum for the solution but I can't seem to make any existing examples work for me.

I have some vba code that pulls values from an access table and I need to put then in an existing worksheet (named Detailed Data) the problem is that this process happens every week so the cells where the data needs to go will change .
I need to find code that will examine a header row for a date value then return the cell address so I know where to insert the data. for example, say today is 1/14 I need to loop thru row 1 to find todays date then retrun the cell address of row2 column3 (where data needs inserted). I have never manipulated and excel spreadsheet from VBA so any help is greately appreciated, Any examples of code that perform operations in excel would also be fantastic.

column1 column2 column3 column4
row1 1/01/06 1/07/06 1/14/06 1/21/06
row2 21 9
row3 19 7
row4 15 12

Thanks for any help you can provide,
Ryan

 

[SkipVought]

Ryan,

Why not use a PivotTable to do your report? It's EXACTLY what you are asking for.



Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

Unfornately, the data has to be pulled from a third party app and populated into a specific spreadsheet that is used across several company locations. The spreadsheet is already creatd, foramtted, etc...... and a complete waste of resources, but anyway I have to do it so...........

 

[SkipVought]

So what. You pull the data into some other sheet. Then you use that data source to pivot a report in the EXACT format you have above.

Should take about 15 seconds to do the Pivot table.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

wish it were that simple, the report is not mine, it's a company wide thing. the excel sheet exists on a corp share, there are cells ready to be filled in and I have to pulg the numbers in.

 

[SkipVought]

Please post an example of the SOURCE DATA that you're importing from Access.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

Maybe more info than you needed but here it is.

basically I take a nasty looking text file (below is a short example the report is actually about 2500 lines) and loop thurgh it using a recordset to pull the numbers that I need into variables in code.
Then I want to plug those variables into excel, the source data is actually stored in variables. for example I use a recordset to loop through this text file to find "job code: 960HNDSTKS" then find the next row containing "2nd Shift-A" then pull the corresponding value of 3.26 from the "Direct" column and store it in a variable. This same process is then repeated about 200 more times to pull different info.

Perf
Shift % Goal Direct
Job Code: 960FSS
1st Shift- A 33 0.51 1.51
Job Code: 960FSXDS
1st Shift- A 999 0.19 0.01
Job Code: 960HNDSTKS
1st Shift- A 61 1.51 2.47
2nd Shift-A 58 3.26 5.64
Subtotal: 960HNDSTKS 59 4.77 8.11
Job Code: 960HNDXDS
1st Shift- A 92 4.09 4.44
2nd Shift-A 565 0.48 0.09
3rd Shift-A 117 3.99 3.40
Subtotal: 960HNDXDS 108 8.55 7.92
Job Code: 960MASTERS
1st Shift- A 49 0.51 1.03
2nd Shift-A 84 1.52 1.80
3rd Shift-A 68 4.00 5.86

 

[SkipVought]

I thought you stated that you were getting data from an Access Table????

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

The text file I posted is imported into an access table

 

[SkipVought]

"I have some vba code that pulls values from an access table "

"The text file I posted is imported into an access table "

Please be clear, concise and complete!

I don't care what text file you "import" INTO Acess, I would like to see the data you get FROM Access that you use for this application.


Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

Skip,

Step 1
"The text file I posted is imported into an access table "
Now I have a table called tblImprortData

Step2
"I have some vba code that pulls values from an access table "........ which is the the table I imported, tblImportData.

the data I get from access is the data in this table. I use a recordset to loop through and store numbers in variables. I don't have a "table" to show you. it's all variables
The part that I need help with is using code to determine where in excel to put my information

 

[SkipVought]

How do I find the REAL last used cell faq707-2115

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

That is great FAQ, sorry I did not find it.

Thanks Skip

 

[SkipVought]

Be sure to register a RATING for this excellent FAQ written by Geoff (xlbo)

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[chanman525]

Good day all,

I am using the following code (found in faq707-2115) to find last row and column used. Could anyone tell me how to modify it to tell me the last column used for a specific row? Thanks for any help

Function FindLastCol()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastCol = c
End Function

Ryan

 

[SkipVought]

UsedRange is not reliable.

function LastRow(Col as variant) as long
  with activesheet
    LastRow = .cells(.rows.count, Col).end(xlup).row
  end with
end function

Skip,

_[red][/red]