View Full Version : [SOLVED:] HELP ON FIND/REPLACE
Marty
05-13-2016, 08:49 AM
Hi all.
I have been trying with no success to find the best formula in VBA for my concern.
Hope someone can help.
I need a VBA formula that would do the following correction : If Style 92500(from Column A all the way down) and ColorCode(From Column B all the way down) is 295, replace ColorCode for 095 in Column B
if not matching, so than dont change anything.
I have several exceptions like that so i would use the same formula and add my exception to my list.
So from the below table, ColorCode for B2 would change for 095, and B3 wouldnt not change because style isnt matching.
A B C
1 Style
ColorCode
Size
2 92500
295
OS
3 92500X
295
OS
thanks for the help.
Hi all...
...I have several exceptions like that so i would use the same formula and add my exception to my list...
Greetings and Welcome to VBAExpress Marty,
I suggest uploading a sample workbook with the data and a description of: exceptions, and what is to happen in these cases.
Mark
Marty
05-13-2016, 10:26 AM
Greetings and Welcome to VBAExpress Marty,
I suggest uploading a sample workbook with the data and a description of: exceptions, and what is to happen in these cases.
Mark
Hi, thanks for the kinds words. I have attached a sample workbook with some description/information, hope you can assist with my request.
Any help is more than appreciated.
Best Regards.
Sub ChangeColorCodes()
Dim Cel As Range
With Sheets("sheet1") 'Adjust as needed
For Each Cel In .Range(Range("A1"), .Cells(Cells.Count, "A").End(xlUp))
Select Case Cel
Case 92500
With Cel.Offset(, 1)
If .Value = 295 Then
.Value = 95
ElseIf .Value = 333 Then
.Value = 42
End If
End With
Case 42424
With Cel.Offset(, 1)
If .Value = 400 Then
.Value = 3
ElseIf .Value = 500 Then
.Value = 42
ElseIf .Value = 600 Then
.Value = 77
End If
End With
Case Etc
End Select
Next Cel
End Sub
skywriter
05-14-2016, 11:08 AM
Looks like you have a typo there.
ElseIf .Vlaue = 600 Then
Marty
05-16-2016, 12:22 PM
Well i did not pass the test ! I am having always the message : "Expected End With" and cannot find how to fix it. I am not yet expert in VBA Excel but trying my very best to get there !
I have tried different corrections but having the same message
thanks for the help. hope you can help!
skywriter
05-16-2016, 03:32 PM
Try putting end with between these two lines.
Next Cel
End Sub
I am having always the message : "Expected End With" and cannot find how to fix itOoops! Sorry. I did not see that error when I posted. I did see the vlaue error and left it, but SkyWriter cheated that for you. :D
SkyWriter has the solution.
Marty
05-17-2016, 05:22 AM
Try putting end with between these two lines.
Next Cel
End Sub
Well you got it! that error is gone ! But i got a "Run-time error '6 - Overflow" and i think it is linked to this line : "For Each Cel In .Range(Range("A1"), .Cells(Cells.Count, "A1").End(xlUp))"
Well you got it! that error is gone ! But i got a "Run-time error '6 - Overflow" and i think it is linked to this line : "For Each Cel In .Range(Range("A1"), .Cells(Cells.Count, "A1").End(xlUp))"
I don't believe that is what Sam had; rather:
For Each Cel In .Range(Range("A1"), .Cells(Cells.Count, "A").End(xlUp))
That said, Cells.Count as the Row argument is a tad suspect. Try:
For Each Cel In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
Also, note where I added the dots, so that Range("A1") and Rows.Count both explicitly belong to "Sheet1 ".
Hope that helps,
Mark
Marty
05-17-2016, 06:35 AM
I don't believe that is what Sam had; rather:
For Each Cel In .Range(Range("A1"), .Cells(Cells.Count, "A").End(xlUp))
That said, Cells.Count as the Row argument is a tad suspect. Try:
For Each Cel In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
Also, note where I added the dots, so that Range("A1") and Rows.Count both explicitly belong to "Sheet1 ".
Hope that helps,
Mark
Mark, I thank you a lot, this is now working good,
thanks to all again for your assistance !
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.