Excel if statement trouble Closed

Question

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.

venkat1926 · Answer

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

Excel if statement trouble

1 response

Similar discussions

Subscribe To Our Newsletter!