VBA - return value in different columns upon a criteria

[mogri]

Hi all,

I would like to have the excel show the net income for each type of day as attached.

[TABLE="class: grid, width: 20"]
<tbody>[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]Expense_Weekday[/TD]
[TD="align: center"]Expense_Weekend[/TD]
[TD="align: center"]Expense_Holiday[/TD]
[TD="align: center"]Income[/TD]
[TD="align: center"]Net_Weekday[/TD]
[TD="align: center"]Net_Weekend[/TD]
[TD="align: center"]Net_Holiday[/TD]
[TD="align: center"]Day[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]492[/TD]
[TD="align: center"][/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]108[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sat[/TD]
[/TR]
[TR]
[TD="align: center"]480[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]750[/TD]
[TD="align: center"][/TD]
[TD="align: center"]270[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Mon[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-70[/TD]
[TD="align: center"]Holiday[/TD]
[/TR]
</tbody>[/TABLE]

The formula is same for all calculation = Column"F" - Column"C" - Column"D"- Column"E" where

The value will be returned in a different column like, if the value in column "J " is Mon,Tue,Wed,Thu or Fri, the value will return in column "G", if the value in column "J" is Sat or Sun, the value will return in column "H" and if the value in column "J" is other, the value will be returned in Column "I".

I have tried tweaking the code found as below yet it still doesn't work. Can anyone help me know where I should modify more to make the code work? Also, can anyone give me any hint what should I write for the case of holiday as it is any word other than the words in Weekday and Weekend?

---------------------------------------------------------------------
Sub Calculate()


Dim LR As Long
Dim i As Long
Dim j As Long
Dim Weekday As Variant
Dim Weekend As Variant


Weekday = Array("Mon", "Tue", "Wed", "Thu", "Fri")
Weekend = Array("Sat", "Sun")
LR = Range("J3:J" & Rows.Count).End(xlUp).Row


For i = 1 To LR

With Range("J3:J" & i)
For j = LBound(Weekday) To UBound(Weekday)
If Cell.Value Like "*" & Weekday(j) & "*" Then
Cell.Offset(0, -3).Value = Cell.Offset(0, -4).Value - Cell.Offset(0, -5).Value - Cell.Offset(0, -6).Value - Cell.Offset(0, -7).Value
Exit For
End If
Next j

For j = LBound(Weekend) To UBound(Weekend)
If .Value Like "*" & Weekend(j) & "*" Then
Cell.Offset(0, -2).Value = Cell.Offset(0, -4).Value - Cell.Offset(0, -5).Value - Cell.Offset(0, -6).Value - Cell.Offset(0, -7).Value
Exit For
End If
Next j
End With
Next


End Sub
---------------------------------------------------------------------

 

[MickG]

Try this:-
NB:- It look like you first 2 values in columns "C & D" are in the wrong columns ??

[COLOR="Navy"]Sub[/COLOR] MG25Jul44
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("J2", Range("J" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] ac = -7 To -5
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, ac) <> "" [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, ac + 4).Value = Dn.Offset(, -4).Value - Dn.Offset(, ac).Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mogri]

Oh, yes...you are correct. The 2 values are in the wrong column. Sorry for the confusion.

Your code really wows me. It exactly gets the answer I had been working for hours without using the condition on the day, and above all it's really short.

Thanks a lot!

 

[DanteAmor]

If cells from C to D are evaluated, it could also be

Sub DAM_Return_Value()
    With Range("G3:I" & Range("J" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=IF(RC[-4]="""","""",RC6-RC[-4])"
      .Value = .Value
  End With
End Sub

 

[MickG]

You're welcome, Even better from "Dante "

 

[mogri]

Thanks a lot, DanteAmor.
Learned how to use R1C1 from your code just now.

 

[DanteAmor]

Thanks a lot, DanteAmor.
Learned how to use R1C1 from your code just now.

I'm glad to help you. Thanks for the feedback.