Find and Replace Value

[premjeetgupta]

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.

 

[NARAYANK991]

Hi Gupta ,

Which are the numbers less than -40 ? -41 , -42 ,... or -39 , -38 ,... ?

Narayan

 

[premjeetgupta]

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".

 

[NARAYANK991]

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

 

[premjeetgupta]

Exactly Sir.

I have multiple excel files so i want to do same changes with all of them.

 

[premjeetgupta]

@Somendra Misra Sir I need a small help on above query. Can u please help ? As Narayan Sir is not online.

 

[Somendra Misra]

Hi Premjeet,

Do the replacement need to be done on each sheet in column F:Q?

Regards,

 

[premjeetgupta]

Mostly it goes till F:Z.

 

[Somendra Misra]

Try below 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]

No the above code not working. When i type input value which is "< -40" to change. it's giving me run time error-13 & Type mismatch.

 

[Somendra Misra]

@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,

 

[premjeetgupta]

Yes, my mistake. It works perfectly. Thank u so much.
Is it possible to changes with all open workbook.

 

[Somendra Misra]

@premjeet

Thanks for the feedback, will have to try. Will see If I can update tonight something.

Regards,