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,
thx in advance.
Example.xls
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--------
thx in advance.
Example.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.