• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and Replace Value

Hi All,

I have an excel file with multiple sheet those contains few values which less than -40. I need a help. Is it possible to find and replace all value which is less than -40 to -39 or -38 or something. Here i am attaching a sample file which can help you to better understand.
 

Attachments

Hi Narayan Sir,

Thank for response.

In attached sheet you can check few of the sheets like "Banglore#2 column no "M", "N" where some value showing -40 same like "Dodballapur" column no "n" where value showing "-45.09".
 
Hi Gupta ,

So do you want that in all the sheets , all values less than -40 i.e. values such as -41 , -42 , -43 ,... should be replaced by any fixed value such as -39 ?

Narayan
 
Try below code:

Code:
Option Explicit

Sub test()

Dim lastRow As Long
Dim lastCol As Long
Dim rng As Range
Dim ws As Worksheet
Dim cell As Range
Dim num As Long
Dim numR As Long

num = InputBox("Input the number which to replace")
numR = InputBox("Input the number to be replace with")

For Each ws In ThisWorkbook.Worksheets
    lastRow = ws.Cells(Rows.Count, 6).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = ws.Range(ws.Cells(2, 6), ws.Cells(lastRow, lastCol))
        For Each cell In rng
            If cell.Value < num Then
                cell.Value = numR
            End If
        Next
Next
           

End Sub

I cannot comment on the speed of macro, which will depends upon no. of sheets, rows & columns.

May be somebody can give a faster macro.

Regards,
 
@premjeetgupta

It worked at my end. I had given two input box, first one will take the input of the number to be changed and second one will take the number to be changed to. See the file.

Edit: you have to type numbers like -40 , -39 in both the input box and not like "< -40".

Regards,
 

Attachments

Back
Top