CODEWITHSUNDEEP
excelsumvba
K.Caller
K.Caller

Reputation: 13

Vba code with =sum

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?

Upvotes: 0

Views: 77

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 4

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

Arash
Arash

Reputation: 150

you need remove this line

data.Value = ""

Upvotes: 3

Related Questions