PDA

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.

GTO
05-13-2016, 09:11 AM
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.

SamT
05-13-2016, 02:18 PM
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

SamT
05-14-2016, 02:26 PM
You passed the test.

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

SamT
05-16-2016, 06:30 PM
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))"

GTO
05-17-2016, 06:26 AM
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 !