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
 
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
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:
1695840862606.png


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).
 
Upvote 0
@

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
 
Upvote 0
Did you try removing that one character from my code and see if it works too?
 
Upvote 0
@

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

End Sub
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
I amended the code a bit to make it more concise:
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
    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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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