Copying values between two matching values...

[JinkyJulie]

Hi all,

Still not getting excel macros as well as I hoped by now..

Have a LONG column (25000 values) that could contain any of eight values... I would like to search the column, find two matching values first instance and next instance and fill the spaces with those values.

Can this be done easily? I cannot...

Thanking you again...

JJ

Apple		Apple
		Apple
		Apple
Apple		Apple
Pear		Pear
		Pear
		Pear
		Pear
Pear		Pear
Cherry		Cherry
		Cherry
Cherry		Cherry

[p45cal]

For the original data in columns a try a macro:

Sub blah()
For Each are In Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas
  If are.Row > 1 Then
    If are.Cells(1).Offset(-1).Value = are.Cells(are.Cells.Count).Offset(1).Value Then are.Value = are.Cells(1).Offset(-1).Value
  End If
Next are
End Sub

[mikerickson]

How about

With Range("A:A")
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error Goto 0 
    .Value = .Value
End With

[JinkyJulie]

Wow!!!! Thank you both so much... both work great... don't understand them, but they work...

Most of my experience is with Word macros... Excel VBA is still very new to me....

Thanks you again!!!!

[mikerickson]

Excel VBA is very similar to Word.
The Excel object model is very different.
Since you are familiar with VBA, the best advice I could give you would be "avoid Selecting, like crazy".
The Macro Recorder is your friend. It will show you what part of the object model that you are working with. (The Object Browser is another, very good friend.) But, it mirrors the user's actions and, therefore, Selects far more often than is necessary.