Why are the two codes below not getting the right formula for myworksheet event change formula?
 

Why are the two codes below not getting the right formula for my
worksheet event change formula? The remaining ones do work, but these
two are not getting the right results.

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

I'm trying to get both column G & H to show a combined value of data
in other columns.

Column G should show result by combining data from column E & B, while
column H should show result by combining data from column F & C.

Column G is showing: =RC[-2]&RC[-5] when it should show
numbers...something like 654154845 if 6541 is in column E and 54845 is
in column B.

Same result with column H.

----------------------

My full worksheet event change/ selection code I have is below is as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("A:A"), Target)
If D Is Nothing Then Exit Sub
For Each C In D

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C 1:R5708C5,4,FALSE))"
' - For Column J
Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'V acation Trip'!
R2C1:R4573C3,2,FALSE))" ' - For Column K
Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C 1:R5392C5,5,FALSE))-
IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
Target.Offset(0, 13).Value = "1" ' - For Column N
Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
[-1])" ' - For Column Q

Next C

End Sub

----------------------------

I appreciate your continued assistance!!

Thanks!

Why are the two codes below not getting the right formula for myworksheet event change formula?
 

On 18 Nov, 10:53, Damil4real wrote:
Why are the two codes below not getting the right formula for my
worksheet event change formula? The remaining ones do work, but these
two are not getting the right results.

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

I'm trying to get both column G & H to show a combined value of data
in other columns.

Column G should show result by combining data from column E & B, while
column H should show result by combining data from column F & C.

Column G is showing: =RC[-2]&RC[-5] when it should show
numbers...something like 654154845 if 6541 is in column E and 54845 is
in column B.

Same result with column H.

----------------------

My full worksheet event change/ selection code I have is below is as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("A:A"), Target)
If D Is Nothing Then Exit Sub
For Each C In D

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C 1:R5708C5,4,FALSE))"
' - For Column J
Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'V acation Trip'!
R2C1:R4573C3,2,FALSE))" ' - For Column K
Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C 1:R5392C5,5,FALSE))-
IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
Target.Offset(0, 13).Value = "1" ' - For Column N
Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
[-1])" ' - For Column Q

Next C

End Sub

----------------------------

I appreciate your continued assistance!!

Thanks!

I got it to work.

Thanks!

Why are the two codes below not getting the right formula for my w
 

Maybe the cells these formula are going into are formatted as text. Try it
with these lines added before your 2 formula lines

Target.Offset(0, 6).NumberFormat = "General"
Target.Offset(0, 7).NumberFormat = "General"

Mike

"Damil4real" wrote:

Why are the two codes below not getting the right formula for my
worksheet event change formula? The remaining ones do work, but these
two are not getting the right results.

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

I'm trying to get both column G & H to show a combined value of data
in other columns.

Column G should show result by combining data from column E & B, while
column H should show result by combining data from column F & C.

Column G is showing: =RC[-2]&RC[-5] when it should show
numbers...something like 654154845 if 6541 is in column E and 54845 is
in column B.

Same result with column H.

----------------------

My full worksheet event change/ selection code I have is below is as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("A:A"), Target)
If D Is Nothing Then Exit Sub
For Each C In D

Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]"
Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]"

Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5708C5,4,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C 1:R5708C5,4,FALSE))"
' - For Column J
Target.Offset(0, 10).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC8,'Vacation
Trip'!R2C1:R4573C3,2,FALSE))=TRUE,0,VLOOKUP(RC8,'V acation Trip'!
R2C1:R4573C3,2,FALSE))" ' - For Column K
Target.Offset(0, 11).FormulaR1C1 = "=RC[-2]-RC[-1]" ' - For Column L
Target.Offset(0, 12).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,M.A.!
R2C1:R5392C5,5,FALSE))=TRUE,0,VLOOKUP(RC7,M.A.!R2C 1:R5392C5,5,FALSE))-
IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))=TRUE,
0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" ' - For Column M
Target.Offset(0, 13).Value = "1" ' - For Column N
Target.Offset(0, 14).FormulaR1C1 = "=RC[-2]*RC[-1]" ' - For Column O
Target.Offset(0, 16).FormulaR1C1 = "=IF(RC[-1]="""","""",TODAY()-RC
[-1])" ' - For Column Q

Next C

End Sub

----------------------------

I appreciate your continued assistance!!

Thanks!
.