none
Find empty cell and fill the "gaps" from a given array RRS feed

  • Question

  • Dear All,

    I do not have better "name" for this topic, but my issue could be a little bit complex. I will try to explain.

    I have many Worksheets with all kind of datas on it. I run several macros to create a report at the end. My problem is that there are 2 columns on each Worksheets which needs to be 100% filled to ensure that the main macro runs perfectly.

    for example:

    Column A: -------- column B:
    ERF -------------------412
    DJS -------------------
    ERF -------------------412
    KJU--------------------523
    HUU -------------------
    CAC -------------------158
    KJU  -------------------523
    ERF -------------------412
    KJU --------------------

    Here for example DJS and HUU does not get a pair. So what i do manually filter for Column B for "Blanks" then i know that for DJS i need to write 225 and HUU 878 (also KJU = 523) (i already got the datas like this, unfortunately i cannot change this). Problem come when i need to check manually 100+rows for the right pair :(

    I am thinking about a simply find and replace function, but i am having problems with the empty cell location, since after the last line all cells will be empty in column B and my macro overloaded. As you see here, the last line is KJU and it is empty (does not matter that it had a value in the previous lines) so if i search for empty cells my macro does not stops here :(

    These pairs are fixed, so i also tried to start with an array replacement but that not worked as well. 

    Does somebody know a simplier solution for this problem in VBA? (so i could eliminate the manual work) I am looking for a kind of check if empty cell in a column then insert data into cell based on another cell value. As I wrote before i have the pairs in advance so i could feed them all into the VBA in advance.

    Many many thanks for all the replies in advance!

    Regards
    Robert





    • Edited by hlpbob007 Wednesday, February 3, 2016 9:59 AM rephrase the main problem
    Wednesday, February 3, 2016 9:43 AM

Answers

  • Hi

    This one will do all your worksheets in the workbook plus I made a small change so that if your last cell in column B is empty, it will fill it. I've used arrays but not to do exactly this type of work, sorry.

    It's easy to make any change in a "Select Case". plus the macro is fast. So that's it.

    Sub Fill_Blanks() 
        Dim Area As Range, LastRow As Long, target As Range
        Dim x As Integer, blk As String
        On Error Resume Next
        Application.ScreenUpdating = False
       
        For x = 1 To Worksheets.Count
            LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
            Sheets(x).Select
            For Each Area In Range("B1:B" & LastRow)

                If Area = "" Then
                    blk = Area.Offset(0, -1).Value

                    Select Case blk
                        Case "ERF": Area.Value = "412"
                        Case "DJS": Area.Value = "9A9" 'change
                        Case "KJU": Area.Value = "523"
                        Case "HUU": Area.Value = "W1W" 'change
                        Case "CAC": Area.Value = "158"
                    End Select
                End If
            Next Area
        Next x
        Application.ScreenUpdating = True
    End Sub


    Cimjet

    Friday, February 5, 2016 4:50 AM

All replies

  • At the moment this is the code what i am using:

    Sub FINDREPLACE()
      
      Dim target, cell As Range
      Dim i As String
      Dim lRow As Long
      Dim kulcs As String
      Dim counter As String
      
      
     i = ""
    counter = 0
        
        
        
      lRow = Sheets("Parts").Range("M" & Sheets("Parts").Rows.Count).End(xlUp).Row
      Set target = Sheets("Parts").Range("N1:N" & lRow)
      For Each cell In target
          If cell.Value = i Then
          kulcs = Worksheets("Parts").Range("M" & cell.Row).Value
                If kulcs = "CAC" Then
                  target.Parent.Cells(cell.Row, "N").Value = "158"
                ElseIf kulcs = "KJU" Then
                    target.Parent.Cells(cell.Row, "N").Value = "523"
                Else
                counter = counter+ 1
                End If
                
          End If
      Next
      
      MsgBox "new stuff found " & counter& "pcs"
      
    End Sub

    Since i have many many "code"s, is it possible to do a kind of "array" replacement instead of if, elseif, elseif, elseif and tons of elseif ? Maybe that also speeds up the runtime :)


    • Edited by hlpbob007 Wednesday, February 3, 2016 11:54 AM
    Wednesday, February 3, 2016 11:53 AM
  • How do you know what number to use for the missing data? Is it something that can be derived from existing data or is it just something you know?

    If you can derive it from existing data, you need to figure out how to convert that logic into code. For example, for any given code, is there one place (or known set of places) where you'd look for the current highest number and then add 1 to give you the next number? If so, write code to do just that.

    If it's not something you can derive but can build a list of paired values to define what you want into any given blank, you can either do the if/then thing or possibly create a sheet with the code/next number pairs and look up the value.

    Which way you go kind of depends on the full problem statement.

    Wednesday, February 3, 2016 2:07 PM
  • It is basically a given pairs.

    So i know that DJS = 225  HUU = 878 and KJU = 523. Basically i can write 50 times elseif and that will do the same. I am thinking now a solution with arrays maybe or for each, i just do not know how to solve it at the moment.

    So something like this
    myarray1=(DJS, HUU, KJU) ; 
    myarray2=(225, 878, 523)

    and check if there is empty cell, then grab the DJS check if it is in the array if yes grab the index, check the other array with the same index, grab the number (225) insert the number... 

    That will be logic what i am chasing on programing right now :)

    Wednesday, February 3, 2016 2:16 PM
  • Hi

    You will need to build your list in the Select Case below. I don't know how long is your list and I never used Select Case for more then 50 so try it first on a sample file.

    Try this :

    Sub Blanks()
        Dim Area As Range, LastRow As Long,blk as string
        On Error Resume Next
        LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
        For Each Area In Range("B1:B" & LastRow)
            If Area = "" Then
                blk = Area.Offset(0, -1).Value

                Select Case blk
                    Case "ERF": Area.Value = "412"
                    Case "DJS": Area.Value = "9A9" 'change
                    Case "KJU": Area.Value = "C9C" 'change
                    Case "HUU": Area.Value = "W1W"  'change
                End Select
            End If
        Next
    End Sub


    Cimjet



    • Edited by Cimjet Wednesday, February 3, 2016 9:25 PM
    Wednesday, February 3, 2016 9:22 PM
  • Hello Cimjet,

    I tried that one, the only thing is with this is the list contains 70-80 elements and i have 10 tabs where i need to run the check. This is why i am thinking of a public constant array or something (only problem yet is that i did not figure out how to assign an array in the very beginning and make it access-able during the whole run) and then just update that array if needed and that's it :)

    Thursday, February 4, 2016 7:48 AM
  • Hi

    This one will do all your worksheets in the workbook plus I made a small change so that if your last cell in column B is empty, it will fill it. I've used arrays but not to do exactly this type of work, sorry.

    It's easy to make any change in a "Select Case". plus the macro is fast. So that's it.

    Sub Fill_Blanks() 
        Dim Area As Range, LastRow As Long, target As Range
        Dim x As Integer, blk As String
        On Error Resume Next
        Application.ScreenUpdating = False
       
        For x = 1 To Worksheets.Count
            LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
            Sheets(x).Select
            For Each Area In Range("B1:B" & LastRow)

                If Area = "" Then
                    blk = Area.Offset(0, -1).Value

                    Select Case blk
                        Case "ERF": Area.Value = "412"
                        Case "DJS": Area.Value = "9A9" 'change
                        Case "KJU": Area.Value = "523"
                        Case "HUU": Area.Value = "W1W" 'change
                        Case "CAC": Area.Value = "158"
                    End Select
                End If
            Next Area
        Next x
        Application.ScreenUpdating = True
    End Sub


    Cimjet

    Friday, February 5, 2016 4:50 AM