Vba code with =sum

Question

I'm using the following

Sub CopyDataByDay()
    Dim data As Range
    Dim today As Date

    Set data = ThisWorkbook.Worksheets(1).Range("A1")
    today = Date

    If (Weekday(today) = vbMonday) Then
        ThisWorkbook.Worksheets(1).Range("B1") = data.Value
    ElseIf (Weekday(today) = vbTuesday) Then
        ThisWorkbook.Worksheets(1).Range("B2") = data.Value
    ElseIf (Weekday(today) = vbWednesday) Then
        ThisWorkbook.Worksheets(1).Range("B3") = data.Value
    ElseIf (Weekday(today) = vbThursday) Then
        ThisWorkbook.Worksheets(1).Range("B4") = data.Value
    ElseIf (Weekday(today) = vbFriday) Then
        ThisWorkbook.Worksheets(1).Range("B5") = data.Value
    ElseIf (Weekday(today) = vbSaturday) Then
        ThisWorkbook.Worksheets(1).Range("B6") = data.Value
    End If

    data.Value = ""
End Sub

But in A1 I have a =sum that gets removed every time I use this VBA. How do I stop the =sum in A1 getting removed?

Well you're explicitly clearing your A1 with data.Value = "", what else should be happening? — GSerg
– GSerg, Commented Sep 3, 2016 at 6:59
Just a general note, your code is "screaming" for Select Case instead of your multiple If and ElseIf — Shai Rado
– Shai Rado, Commented Sep 3, 2016 at 7:11

Siddharth Rout · Accepted Answer · 2016-09-03 07:41:48Z

4

This is not an attempt to answer the question as @Vahid has already pointed out what the problem is. This is more of a pointer on how to write concise code.

Here is the shortest way to write your code

Sub Sample()
    Dim data As Range
    Dim i As Long

    With ThisWorkbook.Worksheets(1)
        Set data = .Range("A1")

        For i = 2 To 7
            If Weekday(Date) = i Then .Range("B" & i - 1) = data.Value
        Next i
    End With
End Sub

Note that the value of vbMonday is 2 and vbSaturday is 7. So you can actually write a loop for it.

Had it been a very big loop, the line

If Weekday(Date) = i Then .Range("B" & i - 1) = data.Value

can also be written as

If Weekday(Date) = i Then .Range("B" & i - 1) = data.Value: Exit For

edited Sep 3, 2016 at 7:41

answered Sep 3, 2016 at 7:25

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GSerg Over a year ago

That is not a good loop. All you need is if Weekday(Date) <> vbSunday then .Range("B1").Offset(Weekday(Date) - 2).Value = data.Value.

Siddharth Rout Over a year ago

I will check out the link but the code you gave is even better :)

Arash · Accepted Answer · 2016-09-03 07:00:37Z

3

you need remove this line

data.Value = ""

answered Sep 3, 2016 at 7:00

Arash

1501 silver badge9 bronze badges

1 Comment

K.Caller Over a year ago

Thank you I have just started using VBA I will do that.

Shai Rado · Accepted Answer · 2016-09-03 07:18:30Z

1

Try to always define and referencing your Sheet object. Also, using Select Case, simplifies it a lot:

Sub CopyDataByDay()

Dim sht As Worksheet
Dim data As Range
Dim today As Date

Set sht = ThisWorkbook.Worksheets(1)

Set data = sht.Range("A1")
today = Date

With sht

    Select Case Weekday(today)
        Case vbMonday
            .Range("B1") = data.Value

        Case vbTuesday
            .Range("B2") = data.Value

        Case vbWednesday
            .Range("B3") = data.Value

        Case vbThursday
            .Range("B4") = data.Value

        Case vbFriday
            .Range("B5") = data.Value

        Case vbSaturday
            .Range("B6") = data.Value

    End Select

End With

End Sub

answered Sep 3, 2016 at 7:18

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

Vba code with =sum

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related