Replace blank cell in the range with string code not working

[ThrottleWorks]

Hi,

I am trying to use below mentioned codes to replace blank cells in the range with a string.

But somehow the code is not working, it is not giving any error but not giving result also.

Can someone help me in this please.

Sheet.Select
Set TempFieldRange = Range(Sheet.Cells(2, 1), Cells(LR6, 1))
            
        On Error Resume Next
        TempFieldRange.SpecialCells(xlCellTypeBlanks).Value = "abc"
        On Error GoTo 0
 
        For Each temp In TempFieldRange
             If temp.Value = "" Then
                temp.Value = "abc"
             End If
          
             i = i + 1
             If temp <> "AAA" Then
                 Cells(i + 1, 3).Value = temp
             End If
        Next

 

[Hui]

The line:
TempFieldRange.SpecialCells(xlCellTypeBlanks).Value = "abc"
replaces all the blanks with "abc"

so there are no blanks left to process later

 

[ThrottleWorks]

@Hui , thanks for the help. Yes, you are right, I guess I have made some error while writing the code.

Right now I am checking the code line by line.

Thanks once again for helping me on weekend ! Have a nice one.

 

[NARAYANK991]

Hi Sachin ,

There are a few issues with the code as it is written.

1. Sheet refers to a worksheet whose codename you have changed to Sheet ; is this correct ?

2. Range(Sheet.Cells(2, 1), Cells(LR6, 1)) is supposed to resolve to a range address ; Sheet.Cells(2, 1) resolves to the address $A$2 , but what does Cells(LR6, 1) resolve to ? LR6 needs to be assigned a value which will be a row number , so that Cells(LR6,1) will resolve to a particular cell in column A.

3. The statement :

On Error Resume Next

is the most misused statement in VBA ; using this statement does nothing other than conceal an error from both the developer and the user. This statement should be used , if at all , when the developer is sure that any errors which are concealed in this manner are not significant errors , which will in no way affect the outcome of the subsequent code.

If at all this statement is to be used , it should be used after the code has been thoroughly tested so that as many errors as possible are correctly handled , either using inline code or using error handlers. Never should this statement be used when you are still developing the code.

4. When you comment out this line of code , and execute , you may well find an error in the statement :

TempFieldRange.SpecialCells(xlCellTypeBlanks).Value = "abc"

This may or may not be doing what you think it should do ; for a surprising caveat to this , see the following link :

http://www.mrexcel.com/forum/excel-questions/371987-problem-specialcells-xlcelltypeblanks.html

The For ... Next loop does something , but whether it does what you want done can be decided only if you explain what exactly you want done.

Narayan

 

[ThrottleWorks]

Hi Narayan Sir, apologies for delay in response. Thanks for the help.
Good night.

I will reply with more details shortly.

 

[ThrottleWorks]

Good Morning @NARAYANK991 Sir ! There were few errors from my side while writing the code.

I was refering to wrong column number. Due to this I was getting wrong results.

Sure, I will use On Error Resume Next with care from now onwards.

Thanks a lot for the help and have a nice day ahead.