I am trying to compare part of a cell with another whole cell. The parts of the cells I need are always between parens (). I need to do this to multiple cells in a column and compare with another cell. More detail...
Cell F6 and F7 contain "Trigger (1234)..." and "Trigger (300)" respectively. The 1234 needs to be compared to I4 and if the same then I need to perform addition on a seperate set of cells, if different then I have to move the 1234 over three cells and perform subtraction on another set of cells AND I need to have Trigger left in the original cell(F6). If there is a second Trigger (i.e. the 300 example) then only the 300 needs to be moved over three cells, Trigger left in F7 and the same subtraction as earlier. Same for a third Trigger.
I could have up to three rows of "Trigger...".
The Trigger data will always be in column F and the compare cell will always be above and in column I. The best way I've found to search for the compare cell is to search for "NYM_HH" and then move over three cells. I'm sure there are better ways.
So far this is a start of what I have, but I am getting Object errors at Value1
Sub Trigger()
Dim Value1 As Range
Dim Value2 As Range
'With Worksheets("Data")
Cells.Find(What:="Trigger", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=")", FieldInfo:=Array(Array(1, 1), Array(2, 9)), TrailingMinusNumbers:=True
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
'ActiveCell.FormulaR1C1 = _
"=IF(RC[1]=R[-1]C[3],R[-1]C[4]+ABS(RC[4]),""Fixed Trigger1"")"
Set Value1 = ActiveCell.Offset(0, 1).Select
Cells.Find(What:="NYM_HH", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set Value2 = ActiveCell.Offset(0, 3).Select
MsgBox Value1
MsgBox Value2
If Value1 = Value2 Then
'Range("g26").Select
'ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=R[-1]C[2], R[-1]C[3]+ABS(RC[3]),R[-1]C[3]-ABS(RC[3]))"
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
Range("j25").Select
ActiveSheet.Paste
Range("k25").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]*RC[-1])"
Rows("26:26").Select
Selection.Delete Shift:=xlUp
Else
End If
'End With
End Sub
Thanks for all your help!!
Cell F6 and F7 contain "Trigger (1234)..." and "Trigger (300)" respectively. The 1234 needs to be compared to I4 and if the same then I need to perform addition on a seperate set of cells, if different then I have to move the 1234 over three cells and perform subtraction on another set of cells AND I need to have Trigger left in the original cell(F6). If there is a second Trigger (i.e. the 300 example) then only the 300 needs to be moved over three cells, Trigger left in F7 and the same subtraction as earlier. Same for a third Trigger.
I could have up to three rows of "Trigger...".
The Trigger data will always be in column F and the compare cell will always be above and in column I. The best way I've found to search for the compare cell is to search for "NYM_HH" and then move over three cells. I'm sure there are better ways.
So far this is a start of what I have, but I am getting Object errors at Value1
Sub Trigger()
Dim Value1 As Range
Dim Value2 As Range
'With Worksheets("Data")
Cells.Find(What:="Trigger", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=")", FieldInfo:=Array(Array(1, 1), Array(2, 9)), TrailingMinusNumbers:=True
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
'ActiveCell.FormulaR1C1 = _
"=IF(RC[1]=R[-1]C[3],R[-1]C[4]+ABS(RC[4]),""Fixed Trigger1"")"
Set Value1 = ActiveCell.Offset(0, 1).Select
Cells.Find(What:="NYM_HH", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set Value2 = ActiveCell.Offset(0, 3).Select
MsgBox Value1
MsgBox Value2
If Value1 = Value2 Then
'Range("g26").Select
'ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=R[-1]C[2], R[-1]C[3]+ABS(RC[3]),R[-1]C[3]-ABS(RC[3]))"
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
Range("j25").Select
ActiveSheet.Paste
Range("k25").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]*RC[-1])"
Rows("26:26").Select
Selection.Delete Shift:=xlUp
Else
End If
'End With
End Sub
Thanks for all your help!!
Last edited: