if statement

  • Thread starter Thread starter bob
  • Start date Start date
B

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,

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?
 
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.
 
Back
Top