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

[patrickmg17]

that	this	another	some more	again	lastly
86468​	4654​	54654​	651651​	Note 1	65168​
Note 2	6546+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

 

[patrickmg17]

Are you really using Excel 365, or some earlier version?

Try changing the word "xlReplaceFormula2" to "xlReplaceFormula" and see if that fixes the issue.

Looks like its actually 2019; gonna try some of the other solutions too but if you know something will 2019 still ill give it a try

 

[Joe4]

Looks like its actually 2019; gonna try some of the other solutions too but if you know something will 2019 still ill give it a try

OK, then you should update your profile:

We often use that when coming up with the solutions. I believe the "xlReplaceFormula2" works in 365, but not in 2019.
However, if you remove the "2" from the end of that, I think it should work (if I recall correctly).

 

[patrickmg17]

@

Akuini​

this seems to have worked perfectly so far! I need to test it with the bigger data sett but I think this may work, I'll hopefully get an answer on it in the next day

 

[Joe4]

Did you try removing that one character from my code and see if it works too?

 

[Akuini]

@

Akuini​

this seems to have worked perfectly so far! I need to test it with the bigger data sett but I think this may work, I'll hopefully get an answer on it in the next day

You're welcome, glad to help & thanks for the feedback.
I amended the code a bit to make it more concise:

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
    Loop While Not c Is Nothing
End With
Application.ScreenUpdating = True

End Sub

 

[patrickmg17]

You're welcome, glad to help & thanks for the feedback.
I amended the code a bit to make it more concise:

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
    Loop While Not c Is Nothing
End With
Application.ScreenUpdating = True

End Sub

Got a new problem While I use excel 2019, the work in question has to be done in excel 2016 and without enableing any add-ons like macros .... which makes everything way harder for no reason. Got any work arounds for this? I'm questioning if its possible now

 

[mohadin]

Hi
The code in #15 works on 2019 unless....

 

[patrickmg17]

Hi
The code in #15 works on 2019 unless....

I need a new solution for excel 2016 without enabled add-ins like macros

 

[Akuini]

Got a new problem While I use excel 2019, the work in question has to be done in excel 2016 and without enableing any add-ons like macros .... which makes everything way harder for no reason. Got any work arounds for this? I'm questioning if its possible now

I'm confused, are you saying you need to this without enabling macros?

 

[Kerryx]

Power query