Excel 2021 vba 7.1 - formula in table in destination worksheet is being deleted when values are transferred to adjacent cells from source worksheet

[BrerRabbit]

I'm copying a number of values from the source worksheet to the table in the destination worksheet using the following code:

Select Case intDCol1
        Case 11 To 28
            Do While intDCol1 <= intDCol2
                Set rngSource = wsSource.Cells(141, intSCol)
                Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
                rngDestGCL.Value = rngSource.Value
                intDCol1 = intDCol1 + 1
                intSCol = intSCol + 1
            Loop

This code is working.

The only problem is that the formulas in the adjacent cells in that row of the destination table are being deleted, even though I'm not copying values to the cells with the formulas in them.

     Case 29, 31, 36, 38
            intDCol1 = intDCol1 + 1
            intSCol = intSCol + 1

I'm trying to use the following code to copy the formula from the cell above it but that isn't working either:

  intDRowLessl = intDRow - 1
  Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
  Set rngDestGCLLess1Row = wsDestGCL.Cells(intDRowLessl, intDCol1)
  rngDestGCL.Formula = rngDestGCLLess1Row.Formula

Various examples from various forums suggest using the following code, but I'm unsure how to translate from R1C1 to Cell coding:

Dim lngLastRow as Long 'declare a variable for the last row

  lngLastRow = Range("F" & Rows.Count).End(xlUp).Row
  Range("G6:G" & lngLastRow).FillDown

How do I resolve this? Thank you in advance.

 

[RoryA]

It's very hard to tell with code taken out of context as we have no idea what the control values are but I'd strongly suspect that you actually are copying blank values to those cells.

 

[BrerRabbit]

It's very hard to tell with code taken out of context as we have no idea what the control values are but I'd strongly suspect that you actually are copying blank values to those cells.

I broke it down to only concentrate on the problem elements. The entire code is:

Private Sub lblUpdateFutureAndScheduledChores_Click()
Dim intRow, intColumn, intLastColumn As Integer
Dim rngSource As Range
Dim rngDestGD, rngDestGCL, rngDestGCLLess1Row As Range
Dim wb As Workbook
Dim wsSource As Worksheet
Dim wsDestGCL, wsDestGD As Worksheet
Dim intDRow, intDRowLessl, intDCol1, intDCol2, intSCol, intSRow, intSColumn, intFRow, intFColumn, intCount As Integer
Dim strMessage As String

    Set wb = ThisWorkbook
    Set wsSource = wb.Sheets("Veggie Sheets")
    Set wsDestGCL = wb.Sheets("Garden Chores List")
    Set wsDestGD = wb.Sheets("Garden Diary")
    
    wsSource.Range("H4").Select
    
    strMessage = "This will take a few moments"
    wsSource.Range("H4").Value = strMessage
    
    'update one off future chores
    If intFColumn <> "55555" Then
        Sheet1.Range("F136:N136").Value = Sheet1.Range("F38:N38").Value
        intCount = 1
        intSRow = 136
        intSColumn = 6
        intFRow = Range("J50").Value2
        intFColumn = Range("J51").Value2
        
        Do While intCount <= 9
            Set rngSource = wsSource.Cells(intSRow, intSColumn)
            Set rngDestGD = wsDestGD.Cells(intFRow, intFColumn)
            rngDestGD.Value = rngSource.Value
            intSColumn = intSColumn + 1
            intFColumn = intFColumn + 1
            intCount = intCount + 1
        Loop
    End If
        
    intDRow = wsSource.Range("G138").Value
    intSRow = wsSource.Range("E141").Value
    intDCol1 = 11
    intDCol2 = 41
    intSCol = 6

    intRow = Range("G138").Value2
    
    Select Case intDCol1
        Case 11 To 28
            Do While intDCol1 <= intDCol2
                Set rngSource = wsSource.Cells(141, intSCol)
                Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
                rngDestGCL.Value = rngSource.Value
                intDCol1 = intDCol1 + 1
                intSCol = intSCol + 1
            Loop
        Case 29, 31, 36, 38
            intDRowLessl = intDRow - 1
            Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
            Set rngDestGCLLess1Row = wsDestGCL.Cells(intDRowLessl, intDCol1)
            rngDestGCL.Formula2R1C1 = rngDestGCLLess1Row.Formula2R1C1
            
            'Sheet9.Cells(intDRow, intDCol1).Formula = Sheet9.Cells(intDRow - 1, intDCol1).Formula
        
            intDCol1 = intDCol1 + 1
            intSCol = intSCol + 1
        Case 30, 37
            Set rngSource = wsSource.Cells(141, intSCol)
            Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
            rngDestGCL.Value = rngSource.Value
            intDCol1 = intDCol1 + 1
            intSCol = intSCol + 1
        Case 32 To 35
            Do While intDCol1 <= intDCol2
                Set rngSource = wsSource.Cells(141, intSCol)
                Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
                rngDestGCL.Value = rngSource.Value
                intDCol1 = intDCol1 + 1
                intSCol = intSCol + 1
            Loop
        Case 39 To 41
            Do While intDCol1 <= intDCol2
                Set rngSource = wsSource.Cells(141, intSCol)
                Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
                rngDestGCL.Value = rngSource.Value
                intDCol1 = intDCol1 + 1
                intSCol = intSCol + 1
            Loop
    End Select
    
    strMessage = "Completed"
    wsSource.Range("H4").Value = strMessage
    Sheet1.Range("E2").Select

End Sub

 

[RoryA]

That code makes very little sense. Your entire Select Case statement is pointless. When it runs intDCol1 is always 11, so the only bit that does anything is the first Case block which copies the values from F141:AJ141 into the destination row columns K:AO. That is probably where your problem stems from.

Also, you should be aware that when you write code like this:

Dim intDRow, intDRowLessl, intDCol1, intDCol2, intSCol, intSRow, intSColumn, intFRow, intFColumn, intCount As Integer

only the last variable is actually declared as an integer; all the rest are variant. You have to specify the type for each variable. And you should use Long not Integer.

 

[BrerRabbit]

That code makes very little sense. Your entire Select Case statement is pointless. When it runs intDCol1 is always 11, so the only bit that does anything is the first Case block which copies the values from F141:AJ141 into the destination row columns K:AO. That is probably where your problem stems from.

Also, you should be aware that when you write code like this:

Dim intDRow, intDRowLessl, intDCol1, intDCol2, intSCol, intSRow, intSColumn, intFRow, intFColumn, intCount As Integer

only the last variable is actually declared as an integer; all the rest are variant. You have to specify the type for each variable. And you should use Long not Integer.

Actually the code works, I've used it several times. It's just the bit where the formula is deleted from the cell when nothing is transferred to that cell.

 

[BrerRabbit]

Actually the code works, I've used it several times. It's just the bit where the formula is deleted from the cell when nothing is transferred to that cell.

I was taught many years ago to declare variables like that, and it has worked each and every single time.

Declaring variables (VBA)

Office VBA reference topic

learn.microsoft.com

 

[RoryA]

Actually the code works, I've used it several times

I don't doubt it runs, but it does not do what you intend. Step through the code and you will see that it never touches any part of the Select Case other than the first Case block.

 

[RoryA]

I was taught many years ago to declare variables like that

No you weren't. Per the article you linked:

In the following statement, the variables intX, intY, and intZ are declared as type Integer.

VBCopy

Dim intX As Integer, intY As Integer, intZ As Integer

In the following statement, intX and intY are declared as type Variant, and only intZ is declared as type Integer.

VBCopy

Dim intX, intY, intZ As Integer

You are using the second syntax, not the first.

 

[Alex Blakenburg]

As Rory has alreay pointed out the code is not doing what you think its doing.
intDCol1 is set to 11, so it is going to do the first case statement.
Inside that case statement it is going to loop through intDCol1 until it hits intDCol2 which is set to 41.
So the entire set of columns from 11 to 41 are performed inside the first case statement.

It is never going to circle back to the Select Case statement so the other cases will never be peformed.

    intDRow = wsSource.Range("G138").Value
    intSRow = wsSource.Range("E141").Value
    intDCol1 = 11
    intDCol2 = 41
    intSCol = 6

    intRow = Range("G138").Value2
 
    Select Case intDCol1                                           ' first pass indDCol1 = 11
        Case 11 To 28                                               ' so it is going to do this
            Do While intDCol1 <= intDCol2                           ' it it going to do this loop 31 times until intDCol1 = 41 being the value of intDCol2
                Set rngSource = wsSource.Cells(141, intSCol)
                Set rngDestGCL = wsDestGCL.Cells(intDRow, intDCol1)
                rngDestGCL.Value = rngSource.Value
                intDCol1 = intDCol1 + 1
                intSCol = intSCol + 1
            Loop

 

[BrerRabbit]

I don't doubt it runs, but it does not do what you intend. Step through the code and you will see that it never touches any part of the Select Case other than the first Case block.

I'm not a newbie. The code works as intended. I tested it for what I needed and then for what it else it did.

Second, I suggest you check the image.

No you weren't. Per the article you linked:



You are using the second syntax, not the first.