Copy first non-blank column to the left using macro

[elizabethlll]

Hello, I'm new to posting though reference threads from time to time - thank you!

I'm trying to create a macro that selects a range of cells in the current column, and then copies information from the cells in the column directly to the left in the range. I have gotten that to work successfully this far with the following (there are 51 cells I'm interested in):

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(51, 0)).Select
Selection.FillRight

This would happen daily, each day is a new column that copies the previous column when the user clicks a button that I've assigned the macro to. My problem is that after every 5 days, there are two blank columns (these represent Sat and Sun, for which no data is entered). So the user would come in Monday, run the macro to populate from the previous "day" (previous column) and it would come back with blanks. Is there a way to write a macro that populates fro the previous "day" (column) unless that column is blank, in which case it would populate from last "day" (column) with values? I should say that it's OK if there are some blank CELLS in the column from which I'm populating, but the whole column can't be blank.

Thank you!

 

[JPARKHURST]

whoops, i made a couple of mistakes in there, sorry, was playing around for a different function, must have copied that in middle. Give me another second.

 

[JPARKHURST]

Here, try this. Paste itno module, call with

call UpdateToday

and the code is:

Sub UpdateToday()
 Dim rSource As Range, rDest As Range
 Dim lOffset As Long
 
 lOffset = -1
 Set rDest = Range(ActiveCell, ActiveCell.Offset(16, 0))
 Set rSource = rDest.Offset(0, lOffset)
    
    Do While Not FindWeekday(rSource)
        lOffset = lOffset - 1
        Set rSource = rSource.Offset(0, lOffset)
        MsgBox ("DoWhile::: rSource: " & rSource.Address)
    Loop
 
 rDest.Value = rSource.Value
 
 Set rSource = Nothing
 Set rDest = Nothing
End Sub


Public Function FindWeekday(rCheck As Range) As Boolean
    If Application.WorksheetFunction.CountA(rCheck) > 0 Then
      FindWeekday = True
      'MsgBox ("Found Weekday")
      Else
        'MsgBox ("Found Weekend")
        FindWeekday = False
    End If
End Function

HTH,

Jon

 

[elizabethlll]

Thanks Jon! Unfortunately, I'm still coming up blank for Monday's in my sheet. Unfortunately, this is far beyond my skills to trouble-shoot!

 

[JPARKHURST]

Here is my sample data. Does this look like your table? I click on Monday, it pushes Friday's data in (it actually gives a message i forgot to comment out first). Let's make sure the structure is what i think it is first, then we'll look.

[TABLE="width: 1092"]
<colgroup><col><col><col><col span="2"><col span="2"><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]Fri 06/15[/TD]
[TD="align: right"]Sat 06/16[/TD]
[TD="align: right"]Sun 06/17[/TD]
[TD="align: right"]Mon 06/18[/TD]
[TD="align: right"]Tue 06/19[/TD]
[TD="align: right"]Wed 06/20[/TD]
[TD="align: right"]Thu 06/21[/TD]
[TD="align: right"]Fri 06/22[/TD]
[TD="align: right"]Sat 06/23[/TD]
[TD="align: right"]Sun 06/24[/TD]
[TD="align: right"]Mon 06/25[/TD]
[TD="align: right"]Tue 06/26[/TD]
[TD="align: right"]Wed 06/27[/TD]
[TD="align: right"]Thu 06/28[/TD]
[TD="align: right"]Fri 06/29[/TD]
[/TR]
[TR]
[TD]data1[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]874[/TD]
[TD="align: right"]86984[/TD]
[TD="align: right"]1033[/TD]
[TD="align: right"]87858[/TD]
[TD="align: right"]88017[/TD]
[TD="align: right"]86984[/TD]
[TD="align: right"]159[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data2[/TD]
[TD="align: right"]357[/TD]
[TD="align: right"]566[/TD]
[TD="align: right"]4654[/TD]
[TD="align: right"]923[/TD]
[TD="align: right"]5220[/TD]
[TD="align: right"]5577[/TD]
[TD="align: right"]4654[/TD]
[TD="align: right"]357[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data3[/TD]
[TD="align: right"]645[/TD]
[TD="align: right"]448[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]1093[/TD]
[TD="align: right"]481[/TD]
[TD="align: right"]1126[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]645[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data4[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]741[/TD]
[TD="align: right"]16541[/TD]
[TD="align: right"]873[/TD]
[TD="align: right"]17282[/TD]
[TD="align: right"]17414[/TD]
[TD="align: right"]16541[/TD]
[TD="align: right"]132[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data5[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]516[/TD]
[TD="align: right"]482[/TD]
[TD="align: right"]673[/TD]
[TD="align: right"]998[/TD]
[TD="align: right"]516[/TD]
[TD="align: right"]325[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data6[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]556[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]892[/TD]
[TD="align: right"]1210[/TD]
[TD="align: right"]1546[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]336[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data7[/TD]
[TD="align: right"]5489[/TD]
[TD="align: right"]9844[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]15333[/TD]
[TD="align: right"]10498[/TD]
[TD="align: right"]15987[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]5489[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data8[/TD]
[TD="align: right"]47984[/TD]
[TD="align: right"]65498[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]113482[/TD]
[TD="align: right"]65501[/TD]
[TD="align: right"]113485[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]47984[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data9[/TD]
[TD="align: right"]3546[/TD]
[TD="align: right"]6579[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]10125[/TD]
[TD="align: right"]7035[/TD]
[TD="align: right"]10581[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]3546[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data10[/TD]
[TD="align: right"]354[/TD]
[TD="align: right"]564779[/TD]
[TD="align: right"]465436[/TD]
[TD="align: right"]565133[/TD]
[TD="align: right"]1030215[/TD]
[TD="align: right"]1030569[/TD]
[TD="align: right"]465436[/TD]
[TD="align: right"]354[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data11[/TD]
[TD="align: right"]654886[/TD]
[TD="align: right"]651[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]655537[/TD]
[TD="align: right"]651[/TD]
[TD="align: right"]655537[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]654886[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data12[/TD]
[TD="align: right"]564[/TD]
[TD="align: right"]56413[/TD]
[TD="align: right"]4655[/TD]
[TD="align: right"]56977[/TD]
[TD="align: right"]61068[/TD]
[TD="align: right"]61632[/TD]
[TD="align: right"]4655[/TD]
[TD="align: right"]564[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data13[/TD]
[TD="align: right"]3366[/TD]
[TD="align: right"]36213[/TD]
[TD="align: right"]6546489[/TD]
[TD="align: right"]39579[/TD]
[TD="align: right"]6582702[/TD]
[TD="align: right"]6586068[/TD]
[TD="align: right"]6546489[/TD]
[TD="align: right"]3366[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data14[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]6546[/TD]
[TD="align: right"]5646[/TD]
[TD="align: right"]6990[/TD]
[TD="align: right"]12192[/TD]
[TD="align: right"]12636[/TD]
[TD="align: right"]5646[/TD]
[TD="align: right"]444[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data15[/TD]
[TD="align: right"]85856[/TD]
[TD="align: right"]16056[/TD]
[TD="align: right"]774[/TD]
[TD="align: right"]101912[/TD]
[TD="align: right"]16830[/TD]
[TD="align: right"]102686[/TD]
[TD="align: right"]774[/TD]
[TD="align: right"]85856[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data16[/TD]
[TD="align: right"]5698[/TD]
[TD="align: right"]654065[/TD]
[TD="align: right"]1147[/TD]
[TD="align: right"]659763[/TD]
[TD="align: right"]655212[/TD]
[TD="align: right"]660910[/TD]
[TD="align: right"]1147[/TD]
[TD="align: right"]5698[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]data17[/TD]
[TD="align: right"]887[/TD]
[TD="align: right"]8746[/TD]
[TD="align: right"]5583[/TD]
[TD="align: right"]9633[/TD]
[TD="align: right"]14329[/TD]
[TD="align: right"]15216[/TD]
[TD="align: right"]5583[/TD]
[TD="align: right"]887[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[JPARKHURST]

And - just in case - try re-copying the text below. If you dont' get an error, you should get a pop-up message which contains the Source Range Address.

Sub UpdateToday()
 Dim rSource As Range, rDest As Range
 Dim lOffset As Long
 
 lOffset = -1
 Set rDest = Range(ActiveCell, ActiveCell.Offset(16, 0))
 Set rSource = rDest.Offset(0, lOffset)
    
    Do While Not FindWeekday(rSource)
        lOffset = lOffset - 1
        Set rSource = rSource.Offset(0, lOffset)
        MsgBox ("DoWhile::: rSource: " & rSource.Address)
    Loop
 
 rDest.Value = rSource.Value
 
 Set rSource = Nothing
 Set rDest = Nothing
End Sub


Public Function FindWeekday(rCheck As Range) As Boolean
    If Application.WorksheetFunction.CountA(rCheck) > 0 Then
      FindWeekday = True
      'MsgBox ("Found Weekday")
      Else
        'MsgBox ("Found Weekend")
        FindWeekday = False
    End If
End Function

 

[elizabethlll]

Your sample data looks good, except I don't have the day in the format. So for instance, I just have 6/26/2018 NOT Tue 06/26. I use a different date table to assign what day of the week, fiscal year, etc.

 

[JPARKHURST]

Did you get this to work, and if not, what is the msg box saying when it comes up?

(remember, if something errs, you have to reset the project before code will run again)

You may try copying that last code bit, just to be sure. Also, uncomment some of the message boxes to see what is going off. Mine is still working each time i have tried.

Thanks,

Jon