Macro is consistently inconsistent

Not_Really_Me

New Member
Joined
May 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I'm entering data from a variety of different responses, the data for which are all stored in excel worksheets. To make it easier, I put together a form (in the code, "Data Entry") which allows me to paste in the data, click a button and have the data automatically copied over into a master worksheet ("Data Collected"). For the most part, this works perfectly, but out of 770 cells there are 8 that it consistently fails to copy to the "Data Collected" worksheet. I've tried using different formatting and different source cells where the data are being pulled from, but it's always the same 8 cells. Pasted below is the relevant code. In myCopy, the cells that are currently being dropped are H5:H12, though the same data are transferred correctly when the section is repeated at the end (H5:H90). Even weirder, when I run the macro from the code viewer, the data appear in those cells briefly, but then disappear after a second or two.


VBA Code:
Option Explicit

Sub UpdateDataCollected()

Dim EntryPg As Worksheet
Dim DataColl As Worksheet
Dim myCopy As String
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCell As Range

myCopy = "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90"

Set EntryPg = Worksheets("Data Entry")
Set DataColl = Worksheets("Data Collected")

With DataColl
    nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With EntryPg
    Set myRng = .Range(myCopy)

End With

With DataColl
    With .Cells(nextRow, "A")
        .Value = Now
        .NumberFormat = "mm/dd/yyyy"
    End With
    .Cells(nextRow, "B").Value = Application.UserName
    oCol = 3
    For Each myCell In myRng.Cells
        DataColl.Cells(nextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With

With DataColl
    On Error Resume Next
        With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1)
        End With
    On Error GoTo 0
End With

End Sub

Any help or suggestions are greatly appreciated!!!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,721
Office Version
  1. 365
Platform
  1. Windows
There is nothing inconsistent that I can see, it's just that you are telling it to clear cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" if they contain hard values.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
No clou at present but you might try the use of a named range instead of the "myCopy" string.
 

Not_Really_Me

New Member
Joined
May 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
There is nothing inconsistent that I can see, it's just that you are telling it to clear cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" if they contain hard values.

Sorry, I'm fairly inexperienced with using macros, what do you mean about clearing cells if they contain hard values?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,721
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

At the end of your code you have
VBA Code:
With DataColl
    On Error Resume Next
        With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1)
        End With
    On Error GoTo 0
End With
Which is clearing the cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" is they contain values, rather than formulae.
I suspect it should be looking at the other sheet.
 

Not_Really_Me

New Member
Joined
May 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
At the end of your code you have
VBA Code:
With DataColl
    On Error Resume Next
        With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1)
        End With
    On Error GoTo 0
End With
Which is clearing the cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" is they contain values, rather than formulae.
I suspect it should be looking at the other sheet.

That was it!!! Thank you so much for point that out, cleared out that section of the code, tried it with a few sample data sets and it consistently works consistently now!

Out of curiosity, can you tell why was it clearing out just those 8 cells?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,721
Office Version
  1. 365
Platform
  1. Windows
I suspect that the intention was to clear the cells on the Data Entry sheet, but you were clearing them on the Collection sheet instead.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,967
Messages
5,627,914
Members
416,282
Latest member
fchagas97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top