Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.