Find and replace macro

[ganeshpoojary05]

Hi,

Can I get a VBA code to replace:

if a cell range is greater than or equal to 06:00 hours then it should replace by "P". If a cell range is less than 06:00 hours and greater than 01:00 hours it should replace by "HD".

My cell range is range("D2:AR300")

Thanks in advance for your assistance.

 

[bappakarmakar]

try this,

Sub replace()
Sheets("sheet1").Select
Dim rng As Range, rng1 As Range, i As Integer, cnt As Integer, j As Integer
Set rng1 = Range("D2:D300")
Set rng = Range("D2:AR300")
cnt = Application.WorksheetFunction.CountA(rng1)
For j = 1 To 41 'D to AR
For i = 1 To cnt
If rng.Cells(i, j) >= 6 Then rng.Cells(i, j) = "P"
If rng.Cells(i, j) < 6 And rng.Cells(i, j) > 1 Then rng.Cells(i, j) = "HD"
Next i
Next j
End Sub

 

[ganeshpoojary05]

I have tried the above code, but its not working. Please note that my Range("D2:D300") is in time format. for example: "6:00:00 AM". If I put that in number format than it works.

 

[bappakarmakar]

I think now it is okk.. try this..

Sub replace()
Sheets("sheet1").Select
Dim rng As Range, rng1 As Range, i As Integer, cnt As Integer, j As Integer
Set rng1 = Range("D2:D300")
Set rng = Range("D2:AR300")
cnt = Application.WorksheetFunction.CountA(rng1)
For j = 1 To 41
For i = 1 To cnt
rng.Cells(i, j) = (Hour(rng.Cells(i, j)) * 60 + Minute(rng.Cells(i, j))) / 60
Next i
Next j
For j = 1 To 41 'D to AR
For i = 1 To cnt
If rng.Cells(i, j) >= 6 Then rng.Cells(i, j) = "P"
If rng.Cells(i, j) < 6 And rng.Cells(i, j) >= 1 Then rng.Cells(i, j) = "HD"
Next i
Next j
End Sub

 

[ganeshpoojary05]

checked but not working fine.