Hi,
If you have a formula in a cell, it is never considered blank, even if the
formula returns an apparent blank.
You will often get this with 'IF' formulas: eg IF(A20=5,Bing,)
If A20 does not equal 5, then the formula returns a .
But XL sees this as a zero-length string, not a blank cell.
You will need different code in your Access code; something that tests for
zero-length strings.
Instead of is not null perhaps len(cell ref)=0
I'm not too familiar with Access, so you'll hav to figure that part out.
Regards - Dvae.
Date: Fri, 19 Mar 2010 11:31:50 -0700
Subject: $$Excel-Macros$$ replace code changes blanks cells
From: mwillia...@socal.rr.com
To: excel-macros@googlegroups.com
I have this code that replaces a - in a column.
It does just that, but when I link that workbook to Access and use a
is not null filter in a query the ones that are blank come up in the
query.
Thus my code is doing something to each cell to make Access think the
cell is not blank (whent it appears to be)
Any ideas???
before code is run
Cell A1...A100 = test-me
cell A101...A65000 = blank
Access query brings up one row, A1
after code is run
Cell A1...A100 = testme
cell A101...A65000 = blank
Access query brings up 100 rows (that also appear blank)
Code:
Dim er As Double
er = Range(o65000).End(xlUp).Row
Range(o2).Select
Do Until ActiveCell.Row - 1 = er
ActiveCell.Value = Replace(ActiveCell.Value, -, )
ActiveCell.Offset(1, 0).Select
Loop
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @
http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about
the learning and support from the group.Let friends and co-workers know they
can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the
words REMOVE ME as the subject.
_
Looking for a new home? With all the latest places, searching has never been
easier.
http://clk.atdmt.com/NMN/go/157631292/direct/01/
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe from this group, send email to
excel-macros+unsubscribegooglegroups.com or reply to this email with the words
REMOVE ME as the subject.
