Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Unanswered: cell by cell walk through

    i have an excel sheet with huge data in a sheet and consist of the same fields af access table like
    area1 area2 area3 area4
    1 2 3 100
    1 2 3 150
    1 2 3 200

    the value for the area4 will always change and i have to compare the field "area4" with the access table field area4
    and if there is a change then color it.

    i have no clue how to do it.. i know how to work with excel automations preety well but i dont know how to assign a condition so as to walk through each and evry cel of excel and compare it..


    any help or suggestions would be appreciated.

    thanks in advance and waiting for your valuable thoughts.

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink quick & dirty code

    here's an example yhat i did for some one trying to fiond first emty cell
    think you'll be able top hack this in to what you want.(sertain you can :-))

    Dim iRow as Int
    Dim iCol as int
    FoundEmty as boolean = false
    irow =0
    icol =0

    do until icol = 1000 or FoundEmty=true

    do until irow =1000 or FoundEmty=true

    if me.workbook.cell( icol , irow ). text="" then FoundEmty = true


    irow =irow +1

    loop
    icol =icol +1
    loop


    if FoundEmty then
    msgbox(" Empty cell found row = " & irow " Column = " & icol)
    else
    msgbox(" NO Empty cell found " )
    end if

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    First off this will be easier if your data is set up with a index field that uses a unique key value. This is usually a acct number, customer number or index number. so your data would look more like this, where pk_field1 is you primary key field:

    pk_field1 field2 field3 field4
    r1 2 3 100
    r2 2 3 150
    r3 2 3 200

    you'll most likely use a Loop control to check each record in your list. You'll either need to bring your Access data into Excel and use a lookup function or use a database connection method to check each record using a query.
    ~

    Bill

  4. #4
    Join Date
    Nov 2007
    Posts
    93

    cell by cell walk through

    thanks for the reply... appreciate it.

    i have the primary set onthe field4 but the first 3 fields cant be set as primary as they are repeated usually thru out the data

    so i am looking forward to set some loop in excel to look for the possible combination... but as i am new to excel vba .. i can hardly find the way for loop terminology..

    could be please sugest me the loop i.e vba code

    thanks and would be looking forward for the reply

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    If you do it the way around it will be easyer

    1 .Import your excel in to Access
    2. Make an SQL

    SELECT *, iif(Excel_sheet.aerea1 <> ACCES_TABLE.aerea1,"DIFFRENT","SAME") AS COMPARE
    FROM Excel_sheet
    LEFT JOIN ACCES_TABLE ON Excel_sheet.aerea1=ACCES_TABLE.aerea1

    3 when done change the query into a make table query (see top menu )
    4 export your table into excell and order irt by the compare column
    make the 'DIFFRENT' RED and the 'SAME' ones Green or so
    the ones without a compare are in your excell sheet but not in acces (if you want more info about that tell me the parameters)

    5 Re-oder on the first column so that your counter will count up again
    6 Your done, take the rest off the day off :-)

  6. #6
    Join Date
    Nov 2007
    Posts
    93

    Cell By Cell Walk Through

    I wrote a query or make table query and exported the table to excel sheet
    i am sending you the excel sheet as an attachment

    and i have shown an example of wht i need in the sheet (manually) .

    i will be anticipating reply from you.

    thanks a lot
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Cross posted at access-world

    Normally if you cross post, it is good etiquette to include other posting.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Question Verry confusing ?????


    SHEET 2 : NY QUEENS NYC high 25

    SHEET 1 : NY Queens Low 400<- =RED
    Shoulder 484
    High 758
    Peak 808


    NOTE: WHEN EVER U GET THE COMBINATION OF AREA=DEST=ORIGIN=SEASON THEN IT SHULD CHANGE THE COLOR OF THE ORIGIN
    FOR INSTANCE IN THE ABOVE :
    NY(AREA)= QUEENS(DEST)=SEASON(LOW) THEN CHANGE COLOR FOR NYC(ORIGIN)
    1st. Why/how do you compare (SHEET2-High) with (SHEET-1 low) ???
    2nd. The diffrence in your sheet don't add up
    3th. The example you give isn't in your sheet. You say : NY- QUEENS-LOW
    while it is NY- QUEENS-HIGH OR NY- MANNHATAN -HIGH

    Please paste an row of your Excel-sheet AND a row of Access as you want to compare those 2.
    And the explain by step what you need like
    Excel :NY Queens Low 400
    Shoulder 484
    High 758
    Peak 808

    Access:NY Queens Low 450
    Shoulder 490
    High 800
    Peak 857
    1.Compare Low with low
    2.then make excel red if it lower, blue when higher
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  9. #9
    Join Date
    Nov 2007
    Posts
    93

    cell by cell walk through

    to make it more simplier i just copied the access table on to the excel sheet

    it shuld look for the same combination as you said.
    Access:NY Queens Low 450
    Shoulder 490
    High 800
    Peak 857
    1.Compare Low with low
    2.then make excel red if it lower, blue when higher

    this is basically my intention to do

    thanks
    and shall be looking forward for hearing from you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •