Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 20th, 2003, 11:58 AM
Authorized User
 
Join Date: Jun 2003
Location: Montclair, CA, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default Variable data Ranges

Hi,
  I'm creating an excel spreadsheet from Access 2002 using VBA to sendout multiple queries to a single spreadsheet (each under a different tab). I then have a master tab that calls the values of each tab to itself. The problem I'm having tho is that certain columns are not always the same, sometimes it's the next one over and when I call for that value, it returns the wrong value becuase it thinks is supposed to bring in that columns value (which would be true if the header would be the right one).

  I have tried if statements and i've come to some kind of limitation of some sort. Seems i can only have 5 if statements nested into a single if statement (total of 6 in 1). Problem with that is that I have 7 columns i need to check values for.

  Is there any other formula or vb code for those 'callings' I'm trying to perform to certain cells ?

Thanks in advance gang!

~Danny
__________________
~Danny
 
Old June 20th, 2003, 12:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It seem from this page, that 7 is the limit. But maybe you can use the solution.

http://www.cpearson.com/excel/nested.htm
 
Old June 20th, 2003, 12:45 PM
Authorized User
 
Join Date: Jun 2003
Location: Montclair, CA, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default

Thank you Birger, I definitly see where/how to go with that. However, I do have one more question on that note. How do I make it reference to a particular cell in my 'master tab'?

For example:
Master tab, cells b2:h2 equal A, D, F, G, H, J, M (respectivly)
other tabs, cells b2:h2 can equal A, F, H, M (not always containing the 7 columns from the master tab, hence this being a variable)

from my master tab i want to check 'other tabs' cells b2:h2 to see if they match the current location in 'master tab'
Example:
if mastertab b2="A", if 'other tab' b2=A, =sum('othertab' b3), 0)
...and so on and so on until i reach h2.

You follow me?

I can send a sample of the spreadsheet if anyone would like to see it.

Thanks again,


~Danny
 
Old June 20th, 2003, 02:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

To be honest, I'm not sure I understand your problem.

I expect Master equivalates an Excel Sheet!

If you would like to check whether a value in one cell corresponds to a value in another cell may use this approach:


Sub x()
    If Sheets("Master").Range("A1").Value = Sheets("Slave").Range("A1").Value Then
    MsgBox "Found"
    Else: MsgBox "Not found"
    End If
End Sub

But if you don't know which colum to compare with, you'll first have to identify the column.

You need to look for a value you know exist or maybe it's enough for you to determine whether a value exists at all.

You may do this by looping rows or columns.

I don't know if this is your problem. I have to leave now, but if you havn't got an answer I'll be glad to try helping tomorrow. I'll look into this site then.

If you're fairly new to Excel, which I'm not sure you are, there might be something interesting here:

http://www.mindspring.com/~tflynn/excelvba.html

Birger




Similar Threads
Thread Thread Starter Forum Replies Last Post
Matching Values between two ranges hood8jmark Excel VBA 2 May 2nd, 2008 09:55 AM
Date Ranges nbuckwheat Access 1 December 12th, 2005 12:45 PM
Copying ranges mtowle Excel VBA 2 November 2nd, 2005 06:35 AM
date ranges yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 December 23rd, 2004 05:23 PM
Variable data ranges for charts Cclhodges Excel VBA 1 June 12th, 2003 02:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.