replace x number of cells in row with y

Hello,

I am trying to program my macro to find 9's and replace with 8's. However it's not quite that simple, I would like my macro to search the row and then replace from the end in. For example I would like my macro to replace for example:

0 9 9 1 1 0 1 0 9 9 9 9

With:

0 9 9 1 1 0 1 0 8 8 8 8

I.e. search from the end of the row if there are 6 9's at the end of the row replace with 6 8's, however those 9's earlier on in the row should remain unchanged.

Is this possible?

Thanks

Z

Kaper,

If I wanted to change what I was replacing 9 to 8 to Z to Y, could I just swap out the 9s for Zs etc. like:

[code]
Sub ReplaceZwithY()
Dim rng As Range
Worksheets("Data").Activate
Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

MsgBox "The cells selected were " & rng.Address

rng.Replace What:=" ", _
Replacement:="Z", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Dim i As Long, j As Long, maxcol As Long, currentrow()
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
maxcol = Cells(i, Columns.Count).End(xlToLeft).Column
currentrow = Application.Transpose(Cells(i, "A").Resize(1, maxcol).Value)
For j = UBound(currentrow) To 1 Step -1
If currentrow(j, 1) = Z Then
currentrow(j, 1) = Y
Else
Exit For
End If
Next j
Cells(i, "A").Resize(1, maxcol).Value = Application.Transpose(currentrow)
Next i
End Sub
[code]

Thank you Kaper.

I am trying to distinguish between not reached and omitted. To do this I need to code all blanks as Z so that there are no blank spaces in my data.

I need to distinguish between not reached and omitted i.e. if a candidate answers questions 1,2,4 and 5 out of 15, I want to code question 3 as Z (omitted) but code questions 6-15 as Y (not reached). The code below works for doing this when omit is 9 and not reached as 8, but when I change 9 to Z and 8 to Y it falls over. Any ideas why or how to get it to work? Thanks.

Please Login or Register  to view this content.

replace 9 with 8 start - Copy.xlsm

Kaper, I attach my workbook here (hopefully). As I say the idea is to turn all of the blanks into Z and then anything that is not followed by an A,B,C or D into a Y.

Thanks again for your help on this one.

Z

Perfect! Thanks Kaper. I have one slight addition to the end of the code, how would I perform a count of my changes in a msgbox? To count how many Z's and how many Ys there are in the spreadsheet?