How do I replace multiple cells of text with the first Number value above them?

patrickmg17

New Member
Joined
Sep 26, 2023
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
thatthisanothersome moreagainlastly
86468​
4654​
54654​
651651​
Note 1
65168​
Note 26546+4
84615​
Note 2
32131​
6545​
Note 2
321651​
98461​
651​
65165​
65651​
Note 2
651651​
651651​
6516511​
6516​
651​
61651​
321651​
Note 1
4984436​
31816​
354684​
161961​
49649846​
65464​
32133​
Note 2
6546514​
619496​
164​
6.52E+08​
3131​
Note 2
351651​

I have the above cells as an example. I want to replace every instance of "Note 2" with the number value above it. So for the first column all 3 "Note 2"s should say 86468. And the ones in column 5 should be 31816. This is the most important part i need, so if you can answer that I'm happy :) but there is another part i need to figure out as well; for every instance of a row having a "Note 1" in it, i need that whole row of data deleted. Most of these rows are were the Note 2 spots pull their replacements so this part has to come after they are replaced.

Using macros or just some simple replace coding would be great, the sheets i go through are 2000 rows long so keep that in mind, THANK YOU :D
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Welcome to the Board!

Within your "block" of data, is it possible to have any empty or blank cells, or will all cells have text or numbers in them?
 
Upvote 0
Assuming that your data begins in cell A1 and there are no blanks in your data, like in your example, this code seems to do what you want:
VBA Code:
Sub MyMacro()

    Dim rng As Range
  
    Application.ScreenUpdating = False
  
'   Set range starting from cell A1
    Set rng = Range("A1").CurrentRegion
  
'   Remove all "Note 2" values
    rng.Replace What:="Note 2", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
      
'   Replace blanks with values from cell above
    rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  
'   Copy formulas to hard-coded value
    rng.Copy
    rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
  
'   Delete all rows where "Note 1" is found
    On Error GoTo err_chk
    Do
        rng.Find(What:="Note 1", After:=ActiveCell, LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Rows(ActiveCell.Row).Delete
    Loop
 
err_chk:
    Err.Clear
    Application.ScreenUpdating = True
  
    MsgBox "Macro complete!"

End Sub

So here is what my sample data looks like:
1695749764041.png


And here is the output after I run the VBA code:
1695749470075.png



 
Upvote 0
Sorry im new to macros,
it says "Compile error: Named argument not found"

Is something wrong or am i just ignorant to something haha
 
Upvote 0
See here for instructions on how to insert a new module and add and run the VBA code.

Also, does your data start in cell A1?

If you get an error message, does it give you a "Debug" option?
If so, click it, and tell me which line of code it highlights.
 
Upvote 0
Yes the data starts in A1, and i already did the same steps as the guide you sent before. Heres what pops up for me, I think something is misnamed somewhere? "FormulaVersion" gets highlighted
1695788105118.png
 
Upvote 0
Power query,this may be a bit bloated but think it does all you need
Book1
ABCDEF
1thatthisanothersome moreagainlastly
286468465454654651651Note 165168
3Note 26546+484615Note 2321316545
4Note 2321651984616516516565651
5Note 265165165165165165116516651
661651321651Note 1498443631816354684
7161961496498466546432133Note 26546514
86194961646.52E+083131Note 2351651
9
10
11thatthisanothersome moreagainlastly
12864686546+484615651651321316545
1386468321651984616516516565651
148646865165165165165165116516651
15161961496498466546432133318166546514
166194961646.52E+08313131816351651
Sheet2


Codes for Table1 - original data
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"that", type text}, {"this", type text}, {"another", type text}, {"some more", type text}, {"again", type text}, {"lastly", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Note 2",null,Replacer.ReplaceValue,{"that", "this", "another", "some more", "again", "lastly"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"that", "this", "another", "some more", "again", "lastly"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table1 (2)", {"Index"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Index", "Attribute", "Value"}, {"Table1 (2).Index", "Table1 (2).Attribute", "Table1 (2).Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each ([#"Table1 (2).Index"] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table1 (2).Index", "Table1 (2).Attribute", "Table1 (2).Value", "Index"})
in
    #"Removed Columns"

After the step adding the index in Table 1 above- copy the Table
and amend the code, basically you are creating a long list and going to filter toshow which row number contains the value Note 1
View attachment 99342
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"that", type text}, {"this", type text}, {"another", type text}, {"some more", type text}, {"again", type text}, {"lastly", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Note 2",null,Replacer.ReplaceValue,{"that", "this", "another", "some more", "again", "lastly"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"that", "this", "another", "some more", "again", "lastly"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Note 1"))
in
    #"Filtered Rows"
1695798079189.png


When you Merge the 2 table using the index and expand the table you will see the rows just filter by null
1695798154824.png
 
Upvote 0
Another option to try:
VBA Code:
Sub patrickmg17()
Dim c As Range
Application.ScreenUpdating = False
With Range("A1").CurrentRegion
    Set c = .Find(What:="Note 2", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        Do
           c = c.Offset(-1)
           Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If

    Do
        Set c = .Find(What:="Note 1", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                c.EntireRow.Delete
            Else
                Exit Do
            End If
    Loop
End With
Application.ScreenUpdating = True

End Sub

Example:
Book2
ABCDEF
1thatthisanothersome moreagainlastly
286468465454654651651Note 165168
3Note 26546+484615Note 2321316545
4Note 2321651984616516516565651
5Note 265165165165165165116516651
661651321651Note 1498443631816354684
7161961496498466546432133Note 26546514
86194961646.52E+083131Note 2351651
Sheet2


Result:
Book2
ABCDEF
1thatthisanothersome moreagainlastly
2864686546+484615651651321316545
386468321651984616516516565651
48646865165165165165165116516651
5161961496498466546432133318166546514
66194961646.52E+08313131816351651
Sheet2
 
Upvote 0
Yes the data starts in A1, and i already did the same steps as the guide you sent before. Heres what pops up for me, I think something is misnamed somewhere? "FormulaVersion" gets highlighted
View attachment 99341
Are you really using Excel 365, or some earlier version?

Try changing the word "xlReplaceFormula2" to "xlReplaceFormula" and see if that fixes the issue.
 
Upvote 0
Perhaps
VBA Code:
Sub test()
    Dim xx As Range
    Dim x As Range
    Dim c As String
    With ActiveSheet.Cells(1).CurrentRegion
        Set x = .Find("Note 2", , , 1)
        If Not x Is Nothing Then
            Do
                x.Value = x.Offset(-1)
                Set x = .FindNext(x)
            Loop While Not x Is Nothing
        End If
        Set x = .Find("Note 1", , , 1)
        On Error Resume Next
        c = x.Address
        If Not x Is Nothing Then
            Do
                If xx Is Nothing Then
                    Set xx = x
                Else
                    Set xx = Union(xx, x)
                End If
                Set x = .FindNext(x)
            Loop While x.Address <> c
            xx.Select
            xx.EntireRow.Delete
        End If
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top