Link to home
Start Free TrialLog in
Avatar of kay soo
kay soo

asked on

How to Lookup a Value and Return Cell Address After Posting in Excel from MS Access

Hi, I am looking for solution how to lookup a value and return cell address after posting in Excel from Access.
I found many examples of how to search string and return cell address online but mainly are for Excel VBA.
From Access form, my intention is to search Column A for a string at the active Excel Workbook for value = "Total" and return with Cell Address (sometimes with multiple "Total" in different Cells in Column A, therefore by using Loop is a must), then programmatically draw Top and Bottom Border line at the two cells next to any "Total" found , as shown in Example.xls.

The code to draw the top and bottom border are as follow,

'----------Draw Line----------
    With xlWSh.Range(xlWSh.Cells( Cell Address "Total" + 1, 2 ), xlWSh.Cells( "Cell Address Total" + 1, 3)).Borders(-4160) 'Top Border
        .LineStyle = xlContinuous
        .Weight = 2
    End With
    
    With xlWSh.Range(xlWSh.Cells( "Cell Address Total" + 1, 2), xlWSh.Cells( "Cell Address Total" + 1, 3)).Borders(-4107) 'Bottom Border
        .LineStyle = xlContinuous
        .Weight = 2
    End With
'----------End--------

Open in new window


thx in advance.
Example.xls
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kay soo
kay soo

ASKER

Thx Norie, your code works great!!

Initially I was thinking to find the Cell address of "Total" follow by manipulate the Right two cells of "Total" Cell Address to draw lines..

Your codes are much more elegance, thx again.