if statement

[bob]

how would i write an if statement that would say if cell in col A=total
then let the range cell = 5 greater then cell containing "total"

ie) if A35="total" then make the range cell =A40

Range(Range("A44"), Range("A44").End(xlDown)).TextToColumns
Destination:=Range("A44"), DataType:=xlDelimited, _
'TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo
_
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1))

thanks

 

[Bob Phillips]

Bob,

I'm sure it's me,. but your question confused me.

What is the range cell?
And what does that bit of VBA have to do with it?

 

[Dave Peterson]

You could do it with a Find.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim myCell As Range
Dim WhatToFind As String

WhatToFind = "Total"
With ActiveSheet.Range("a:a")
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox WhatToFind & " was not found!"
Else
Set myCell = FoundCell.Offset(5, 0)
MsgBox myCell.Address
End If

End With

End Sub

VBA's help is pretty good with the .find method.