Excel if statement trouble

[Closed]
Report
-
 ahartle -
Hello,
I have looked all over and found nothing. I am trying to make a formula so that if a cell that contains Profit Center Total: is below a cell that contains Venue A then next to the cell of Profit Center Total: it places Venue A

example of data
Venue A
ID
1
2
3
4
5
15
16
17
Profit Center Total:
Venue B
ID
1
2
3
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
Profit Center Total:
Venue C
ID
1
2
80
81
82
83
84
Profit Center Total:
Venue D
ID
1
2
3
4
5
6
7
8

Not sure if i am being clear or not. Please help.

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
you have not given complete information

I assume except for column A all other columns are BLANK.

if this so the following macro will work. if not give complete information and the macro needs modification
(the profit center total for venue D is not there. If it is there even then the macro will work.

Sub test()
Dim x As String, cfind As Range, venue As Range
Dim add As String
x = "Profit Center Total:"
Worksheets("sheet1").Activate
On Error Resume Next
Set cfind = Columns("A:A").Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
Set venue = cfind.Offset(0, 1).End(xlUp).Offset(0, -1)
'msgbox venue
cfind.Offset(0, 1) = venue
End If
Do
Set cfind = Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
Set venue = cfind.Offset(0, 1).End(xlUp).Offset(0, -1)
Set venue = venue.Offset(1, 0)
'msgbox venue
'msgbox cfind.Address
cfind.Offset(0, 1) = venue
Loop

End Sub

Thanks for the help. I just need it to go through the file and place the venue next to the profit center, then i have already created an if statement that does the rest of what i need to do. I am not familiar with macros so you will have to be more clear. THANKS in advance