UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Find And Replace Blanks (vba), Any Version    
 
   
ws1o
post Jan 3 2020, 11:03 AM
Post#1



Posts: 116
Joined: 30-October 15
From: Central CT USA


I'm running some VBA code from an MS Access application that has to fix up values in an Excel worksheet.

It needs to go through a huge worksheet (500,000 rows) and find all blank values in Column D and replace them with the values from column A (in the same row as the blank)

I wrote some code that walks down the rows and does this, but it's glacially slow.

Then I tried this, but it just replaces the blanks with the header from row 1 of column A:

CODE
.Columns("AG").Replace What:="", Replacement:=.Columns("AD"), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


Any suggestions?
Go to the top of the page
 
June7
post Jan 3 2020, 04:03 PM
Post#2



Posts: 1,244
Joined: 25-January 16
From: The Great Land


If you only want to replace Column D with values from Column A, why reference multi-column range? I tried your code and I get "type mismatch" error on the Replacement argument. If I use a literal value like "X", no error but no edits either. I change "AG" to "D" and it does replace empty cells with "X"

So I am not sure this method can do the dynamic reference you want.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th February 2020 - 05:54 AM