PDA

View Full Version : [SOLVED] Copying values between two matching values...



JinkyJulie
01-07-2016, 10:22 AM
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...:mkay

Thanking you again...

JJ



Apple


Apple





Apple





Apple



Apple


Apple



Pear


Pear





Pear





Pear





Pear



Pear


Pear



Cherry


Cherry





Cherry



Cherry


Cherry

p45cal
01-08-2016, 01:05 PM
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
01-09-2016, 06:16 PM
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
01-14-2016, 08:47 AM
Wow!!!! Thank you both so much... both work great... don't understand them, but they work... :hi:

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

Thanks you again!!!!

mikerickson
01-14-2016, 06:15 PM
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.