Trouble looping through a MS Excel range

[socomfort]

Hello:

I am trying to create a looping structure that finds a value and compares it to another value on two different spreadsheets using VBA.

I have already figured out how to push MS Access data to MS Excel into a blank sheet. I need to be able to loop through the data pushed to excel by the first column which is "JobNumber" and match it to a column (list) of job numbers on another sheet:

Output to Excel:

Sheet1
JobNum PartNum Reference Cost
110 ... ... $
229 ... ... ...
303 ... ... ...

Sheet2
JobNum
110
229
303

I realize that I can use "Match" or VLookup within Excel to bring back the values I need. However, I am creating other objects using VBA with this data; I'd like to keep it all within the IDE where possible.

I am familiar with recordsets and qdefs within MS Access. And essentially, I am trying to use JobNum as my criteria within a recordset in order to bring back the other field values I need.

I started learning about named ranges, but when I tried to Dim rs and create the loop, the problems began. Does anyone have any experience with this?

thanks,

Ben

 

[SkipVought]

Ben,

1. I use the Match & Index worksheet function in VBA, like...

Dim vRow 
vRow = Application.Match(MatchValue, Range(strYourNamedRange), 0)

2. I PULL the data into Excel from MS Access, Oracle, DB2, Excel or Text files using MS Query via Data/Get External Data...

So what's your specific question? Please post any relevant code.

Skip,

_[red][/red]

 

[socomfort]

Hi Skip:

Thank you for your response. I am rather new a this and I am still in the design phase of this routine. What I am trying to do is to match fields (Job Number) in two different spreadsheets.

I have a named range in one sheet called Range1 that contains only the Job Number in a column. On the second sheet I have my export from a MS Access Db (Range2). It begins with Job Number (left most column) and contains several other fields.

I am trying to lookup the values in Range1 and match them to the Job Number in Range2, thereby returning the other fields. I read about recordsets and I now know a bit about looping structures, but I am not sure where to begin. Do I need to declare an array of some sort?

Do you know of any resources I could read that would show me some VBA examples that deal with this?

I am sorry I don't have any code to show you yet. I appreciate any help you can provide.

thanks,

Ben

 

[SkipVought]

Have you FIRST tried doing the lookup sucessfully on the spreadsheet, using your named ranges?

Skip,

_[red][/red]

 

[lickity40]

Hello and forgive my jumping into this thread without an answer, but I have a very similar situation.

I too have linked to a db table successfully and it is Sheet 1.

My Sheet 2 is a template copy of the remaining sheets that has vlookup formulas that brings the data over that is sorted according to the format of the other sheets in the workbook.

Sheet 1
Employee TotHrs OT Sick VAC
Doe, J. 70 0 0 0
Smith, R. 63 0 7 0
Jones, S. 35 0 0 35

Sheet2
Employee TotHrs ...
Doe, J.

=VLOOKUP(B6,'Sheet 1'!$A$2:$N$79,9,FALSE)

(Resulting in 70 in this case)

The formatting on the remaining sheets is such that the data records are grouped by department # then by employee name with sub-totals after each department.

My objective is to get the resulting data from my sheet 2, to an existing sheet in the form of a macro. I could manually copy and 'paste special' the values only, but that would be labor-intensive.

Excel doesn't allow multiple copy/cut paste. I did find VB code that will allow this but not paste.special. (which means I get all of the formulas copied over.

I wrote some code that hides all of the blank and sub-total rows, and using the Edit>GoTo>Special>VisibleData to copy/paste, I thought this would work, but it does not.

So, after many hours of failed attempts, I'm feeling helpless.

One thought I had in mind was if I could write some vb code that contained vlookup, it might work.

It would go something like this.

Reference Sheet PPE@1.20.07, (her named sheet), A7:A200, (Employee Names), and lookup from Sheet 1 A1:A200. Select the matching rows and copy the values into PPE@1.20.07.

Forgive the length of this but a programmer I ain't!

Thanks in advance!!!

Rick

 

[Bong]

I know it's fashonable, and probably even good practice to use the spreadsheet functions that Excel offers when using Excel, but I find it easier to just code what it is I want to do. I figure, I'm already writing code anyway.

So, what I would do is loop through the source data and write out the stuff you want to the target.

It might be something like this:

with sheets(2)
   for rw=2 to 79
     for cl=1 to 15
       if .cells(rw,cl)=<your criteria> then
          sheets(<whatever>).cells(rw,cl)=.cells(rw,cl)
       end if
     next
   next
end with

_________________
Bob Rashkin

 

[lickity40]

Thanks Bob!

Ok..I understand the English part. <g>

Again, I'm not a programmer. I got roped into this one but did learn a lot over the past week.

It sounds like you're saying to refer to my data source sheet, "Sheet 1". You're identifying the range of source data to examine. Am I on the right track?

I'm stuck on the "<your criteria>".

if .cells(rw,cl)= I assume here that I need to write something to the effect of "If a found name in A2:AX of the source data is also found in A2:AX fo the destination sheet, copy that row of data over".

Am I on the right track?

Thanks again!

Rick

 

[Bong]

Put simply, yes.

If you can write out what you want to do (algorithmically), it will be clear to us, and you, what the steps should be. So,

a) you want to look at a range on a sheet (what sheet? what range)
b) based on what you find there (what, exactly), you want to populate another sheet (what sheet? what range?) with values from some of the cells of the first sheet (which cells?)

something like that.

_________________
Bob Rashkin

 

[lickity40]

Algorithmically? I'm thinking that's a programmer's term. <g>

Great...so I must be 'thinking' like a programmer to have understood that. Just can't write it out. Tried in my above example but guess it had too much English lol

Hmm...Ok. Data table def's.

Sheet 1. (that's really the name of it)
Unique data for reference: A1..A100. (Employee [LName], [FName])
Data to copy over to Sheet 2; B1:G100

Sheet 2. (Destination Sheet)
Data to reference in Sheet 1, A1:A100. (Employee [LName], [FName])
Data to copy over if there's a match: B6:G106 (header offset)

Man, I know I'm close here. Please don't let me down Bob! lol

Thanks!

 

[Bong]

OK. As I understand it, you look down column A in Sheet2. For every name you find there, you look in column A in Sheet1 for the same name and when found, copy the data in that row from columns B through G onto the row in Sheet2 with the same name. Right?

There are lots of ways to make this elegant but why not let the computer do the work?

for rw2=1 to 100
   name = sheets("Sheet2").cells(rw2,1)
   for rw1=1 to 100
      if sheets("Sheet1").cells(rw1,1)=name then
          for cl=2 to 7
             sheets("Sheet2").cells(rw2.cl)=sheets("Sheet1").cells(rw1,cl)
          next
      end if
   next
next

_________________
Bob Rashkin

 

[lickity40]

Thank you Bob,

On the surface, this makes sense to me. When I run it though, I get the error message, "Run-time error '424': Object Required. When I debug, here is what is highlighted:

===> Sheets("Sheet2").Cells(rw2.cl) = Sheets("Sheet1").Cells(rw1, cl)

I verified the names of the sheets. The ranges in each sheet are identical. <?>

Stumped...

Rick

 

[PHV]

Sheets("Sheet2").Cells(rw2[!], [/!]cl) = Sheets("Sheet1").Cells(rw1, cl)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lickity40]

PH,

THAT was it! Thanks to you AND Bob! I hope this does it. I did find one issue that I might be able to work out. I need an exception to this rule for the name "Total". There are a few labels with totals and they are carrying over.

Outstanding!

Rick