Fill in the Blanks - VBA

**hillmas83** · 07-17-2015, 05:31 PM

Hi,

i would like a macro in VBA which fills in the blanks in the below report.

Fill Blanks.PNG

i normally do this by inserting the relevant number of columns and inserting the formula

Please Login or Register  to view this content.

however this is very repetative and i am doing this alot of times throughout each day.

i have written some code but i am just a beginner. See below current code -

Please Login or Register  to view this content.

this code works, but if the number of columns (In purple on the left) changes then it falls over. Also i would like the blank cells in green to be filled in with zero's. Please ignore the fact some of the numbers in green have 'EA' next to them. These are treated as just numbers.

many thanks.

**tfurnivall** · 07-17-2015, 05:46 PM

It would help if you would describe - in plain language - what you want to accomplish by this. Just scanning the portion of the spreadsheet that you quote, it looks (assuming that A1 is the top left corner) that you want to replace a missing Customer ID (Ship-To Party) with a color (part of Cat. Year/Month). I've experienced many customers who make me see RED or feel rather BLUE, but I don't know that I'd want to try and ship them anything;-)

It is tedious to put all that stuff down on paper, but it has the marvelous side-effect of helping clarify your goals and methods!

Thanks,

Tony

**FDibbins** · 07-17-2015, 05:51 PM

Also, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

**hillmas83** · 07-17-2015, 06:14 PM

thanks for the above comments and apologies that i was not clear on what i wanted.

tony, if it helps the below is what i want it to look like in the end

Fill Blanks2.png

Also, Please upload a sample of your workbook, not a picture of your data

i would love to as would be a lot easier for me and you but it says the file size exceeds the forum limit of 1000KB :-(

**FDibbins** · 07-17-2015, 07:07 PM

If your file is too big, reduce the amount of data. All we need is a small representative sample of your data...inlclude only what is relevant to your question. If necessary, put a dummy workbook together.

Many membe4rs (muyself included) cannot even see your pic

**Marc L** · 07-17-2015, 09:03 PM

PHP Code:


Sub Demo()
    With Cells(1).CurrentRegion
        VA = .Value
        
        For R& = 2 To UBound(VA)
            For C& = 1 To 3
                If VA(R, C) = "" Then VA(R, C) = VA(R - 1, C)
            Next
            For C = 5 To UBound(VA, 2)
                If VA(R, C) = "" Then VA(R, C) = 0
            Next
        Next
        
        .Value = VA
    End With
End Sub

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

**Marc L** · 07-18-2015, 12:13 AM

PHP Code:


Sub Demo2()

    With Cells(1).CurrentRegion.Columns

        With .Item("A:C")

            If Application.CountBlank(.Cells) Then

                .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

                .Formula = .Value

            End If

        End With

        With .Item(5).Resize(, .Count - 4)

            If Application.CountBlank(.Cells) Then .SpecialCells(xlCellTypeBlanks).Value = 0

        End With

    End With

End Sub

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

**hillmas83** · 07-18-2015, 05:29 AM

Hi Marc,

i tried your code but i got an error.

All,

please see attached workbook. Sheet 1 is how it looks to start with and sheet 2 is how i would like it to look after running the macro. It is basically saying, if the cell (A:D) is blank, fill it with the above cell.

The area in purple (columns A:D) can change. Can be 3 columns, could be 7. Also the amount of columns (Months) can be anywhere from 1 to 100.
The blue area contains numbers and i would like the empty cells to be filled with a zero.

I should also say, i would like the new output to be in sheet 2 and to keep sheet 1 as it was.

i hope this is a better explanation :-)

many thanks,
Marc

**XLRATOR** · 07-18-2015, 06:14 AM

Hello There,
Try this:

Please Login or Register  to view this content.

Regards.

**Marc L** · 07-18-2015, 08:25 AM

Originally Posted by hillmas83

i tried your code but i got an error.

Which code, which error, how can I guess ?‼
Both codes work on my side, so issue comes from yours …

Better is to crystal clear explain your needs at first post !
Last try, this code based upon your sample workbook works too on my side :

PHP Code:


Sub Demo2a()
    Dim Rg As Range

    With Sheet1.Cells(1).CurrentRegion
        C = Application.Match("Cal. year / month", .Rows(2), 0)
        If IsError(C) Then Beep: Exit Sub
        Sheet2.UsedRange.Clear
        Application.ScreenUpdating = False
        .Offset(1).Copy Sheet2.Cells(1)
    End With

    With Sheet2
        With .Cells(1).CurrentRegion.Rows
            With .Item("2:" & .Count).Columns
                With .Item(1).Resize(, C - 1)
                    If Application.CountBlank(.Cells) Then
                        For Each Rg In .SpecialCells(xlCellTypeBlanks)
                                 Rg.Value = Rg(0).Value
                        Next
                    End If
                End With
                With .Item(C + 1).Resize(, .Count - C)
                    If Application.CountBlank(.Cells) Then .SpecialCells(xlCellTypeBlanks).Value = 0
                End With
            End With
        End With
    End With
End Sub

You should like it, so thanks to click on bottom left star icon « Add Reputation » !

@ XLRATOR : un peu léger ton code …

**XLRATOR** · 07-18-2015, 08:34 AM

Hello All, Bonjour MARCL,

@ XLRATOR : un peu léger ton code …

All I was thinking it is about, is to replace blank Cell Values by Zeros, at list this is what our guy is asking for by saying:

i would like a macro in VBA which fills in the blanks in the below report.

Merci Marc.

**XLRATOR** · 07-18-2015, 08:42 AM

Hello All, Bonjour MARCL,

@ XLRATOR : un peu léger ton code …

All I was thinking it is about, is to replace blank Cell Values by Zeros, at list this is what our guy is asking for by saying:

i would like a macro in VBA which fills in the blanks in the below report.

@Marc, D'aprés ce que je voie, la quéstion du gars n'est jusque là pas trop claire.

Merci quand même.

**Marc L** · 07-18-2015, 08:42 AM

Salut ! (Hi !)

Its needs was highlighted in post #8 …

Anyway, could you try my Demo2a code and returns any issue ?
Merci. (Thanks.)

**XLRATOR** · 07-18-2015, 08:50 AM

I've tried MARCL's Code, it works fine for me.

**Marc L** · 07-18-2015, 08:57 AM

Merci !

I've just optimize it (post #10) …

**hillmas83** · 07-18-2015, 09:00 AM

Marc L,

Thank you for this code. It works...but...when their are more columns before the dates it fills them in with zeros instead of what is in the above cell. The number of columns before the first date can change. Can this be amended?

many thanks,
Marc

**Marc L** · 07-18-2015, 09:07 AM

Demo2A already does that ‼ Just read in code how C variable is initialized …

But can't work against erratic worksheet with no brain logic onboard ! Post a sample workbook to see if any logic inside …

**hillmas83** · 07-18-2015, 09:13 AM

Hi,

see attached sample workbook. In columns E and F it puts in zeros.

**XLRATOR** · 07-18-2015, 09:13 AM

I've just optimize it (post #10) …

That's the one I've tried.

**Marc L** · 07-18-2015, 09:46 AM

Last last try :

PHP Code:


Sub Demo2b()

    Dim Rg As Range



    With Sheet1.Cells(1).CurrentRegion

        For Each Rg In .Rows(3).Cells

              If Rg.NumberFormat = "#,##0" Then C& = Rg.Column: Exit For

        Next

                                            If C = 0 Then Beep: Exit Sub

        Sheet2.UsedRange.Clear

        Application.ScreenUpdating = False

        .Offset(1).Copy Sheet2.Cells(1)

    End With



    With Sheet2.Cells(1).CurrentRegion.Rows

        With .Item("2:" & .Count).Columns

            With .Item(1).Resize(, C - 1)

                If Application.CountBlank(.Cells) Then

                    For Each Rg In .SpecialCells(xlCellTypeBlanks)

                             Rg.Value = Rg(0).Value

                    Next

                End If

            End With

            With .Item(C).Resize(, .Count - C + 1)

                If Application.CountBlank(.Cells) Then .SpecialCells(xlCellTypeBlanks).Value = 0

            End With

        End With

    End With

End Sub

fleche.gif You must like it ! So thanks to click on bottom left star icon « Add Reputation » !

**hillmas83** · 07-18-2015, 10:05 AM

thank Marc. We are so nearly there. the only issue now is that the data does not start in Cell A1...its actually starts in A31 and there is a lot of stuff above it which i want to ignore. With your code, if there are lines above the data it doesnt start the macro.

**hillmas83** · 07-18-2015, 10:08 AM

see attached for example of above

**Marc L** · 07-18-2015, 10:24 AM

You must give all your needs at once !

Any other point ? It will be my last mod !

**hillmas83** · 07-18-2015, 10:24 AM

sorry, no this is it now i promise :-)

**Marc L** · 07-18-2015, 10:49 AM

So the mod is beginner level, you could have done it yourself !

Just amend codeline #4 like this : Sheet1.Cells(30, 1).CurrentRegion …

You must like it ! So thanks to click on bottom left star icon « Add Reputation » !

Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Hi ! Try this demonstration !

Try this way without loop !

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Try this !

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Try this !

Re: Fill in the Blanks - VBA

Re: Try this !

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Re: Fill in the Blanks - VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Fill in the blanks!

[SOLVED] RE: Fill in the blanks

[SOLVED] Fill in the blanks

Bookmarks

Bookmarks

Posting Permissions