0

I have a file with a bunch of rows that contains data for certain part numbers from different configurations. Some of these part numbers are repeated throughout the file, and in those duplicated part numbers may contain certain data and some may not. I am trying to find the best way to determine the commonalities in the file for certain data. So for the commonalities, if one row has a value and another row is blank, the value for the nonblank row would be put into the blank row. And if the data on those two rows were different it would change the font color on the cell indicating that this part number two different unique values and should be checked.

Dim i, j, n As Long
Dim lr As Long
Dim moaf As Workbook
Dim sht As Worksheet

Application.ScreenUpdating = False

Set moaf = Workbooks("MOAF3.xlsb")
Set sht = moaf.Worksheets("Wire Data")

n = InputBox("What column # are you trying to fill in?: ")
lr = Cells(Rows.count, 2).End(xlUp).Row

For i = 2 To lr
    lkup = Cells(i, 2).Value 'sets first lookup value
    Fill = Cells(i, n).Value 'sets the first data value to compare
    If Len(Fill) > 0 Then
        For j = 2 To lr
            lkup2 = Cells(j, 2).Value 'sets the second lookup value
            Fill2 = Cells(j, n).Value 'sets the second value to compare
            If lkup2 = lkup Then 'checks to see if p/ns are same
                If Len(Fill2) = 0 Then 'checks to see if second value is blank
                    Cells(j, n).Value = Fill 'if value is blank the cell takes value of non blank     cell
                ElseIf Fill <> Fill2 Then 'checks to see if the values are non matching and non zero
                    Cells(i, n).Font.ColorIndex = 3 'changes font color of two cells
                    Cells(j, n).Font.ColorIndex = 3 'changes font color of two cells
                End If
            End If
        Next j
    End If
Next i
Application.ScreenUpdating = True
End Sub

Doing this generally freezes my excel, where my computer has 32GB of RAM and is Windows10. Is there a better approach for my problem, or is it something that can be done without using a vba? I've done some research on a method without using vba, but with like sumifs, countifs but haven't really done any deep dives.

Jeff
  • 43
  • 1
  • 6
  • 1
    Does your code work fine on a smaller data set? If yes, this question may be better suited to https://codereview.stackexchange.com/ That said, you may want to look into having the first loop be `i = 2 To lr - 1` and the second loop be `j = i + 1 To lr` – cybernetic.nomad Jan 29 '20 at 16:14
  • Can you give some short sample data, and expected output? I'm wondering if a conditional format might work? Also, you can try using `Application.Calculation=False` to turn off calculation which could increase the speec. Also your ranges are all unqualified...You declare `sht` but never link a range to it... – BruceWayne Jan 29 '20 at 16:15
  • I did a step through in the beginning to test each instance of the code and it worked. So for example: p123 has a parameter of A and p123 is also present further down but doesn't have that parameter listed. It will put the parameter of A in that blank space further down. This way I don't have to manually filter through every lookup value or part. I know I realized that I don't use the sht value. I had multiple workbooks open when I initially wrote it but decided when I run it I'll just have the file open. – Jeff Jan 29 '20 at 16:24
  • @brucewayne I'm not too familiar with turning calculations off. Is really putting in a value when blank considered a calculation. Will turning it off prevent from the cells from getting populated when screen updating gets turned back on? – Jeff Jan 30 '20 at 00:30

2 Answers2

0

So, if I understand your question correctly, you start with following data:

ID Column_header
 2             a
 3       _BLANK_
 4       _BLANK_
 5             b
 6       _BLANK_

And you want to turn this into:

ID Column_header
 2             a
 3             a
 4             a
 5             b
 6             b

I know a very simple trick for that (I have put everything in column 'A' for explanation):

  • Select every cell inside that column
  • Goto (Ctrl+G) Special, Blanks
  • In the formula bar, type =A2 (you are currently located in 'A3', and you want to copy there the value of the cell just above it)
  • Press Ctrl+ENTER

You'll see that 'A2' gets copied into 'A3', 'A3' into 'A4' and 'A5' into 'A6' (the fact that this is done for all blank cells, is due to the Ctrl+ENTER).

Record this into a macro, and it will go much faster.

I already see a question popping up : "Ok, but what about the font colour I want to change?". Well, the newly filled cells are based on a formula, so the length of =FORMULATEXT() won't be zero. You use this as a basis for conditional formatting.

Good luck

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • Yes this is pretty much it but for each blank being populated, they have a separate column that has a common value that determines where to put it. – Jeff Jan 29 '20 at 17:04
  • I don't understand exactly what you mean by that, but do you think you can get it done modifying the formula? – Dominique Jan 29 '20 at 19:29
  • index look parameter 2 L1 a 3 L1 ? 4 L2 c n L1 ? for 2 and 3 the value depends on the lookup and if they are different it colors. – Jeff Jan 29 '20 at 20:13
  • ? Sorry, I don't understand what you mean. Can you edit your question, showing how your sheet looks like and what it should look like after the macro? – Dominique Jan 30 '20 at 16:30
0

The inner for loop just needs to start at i, that is:

for j = i to lr

This should roughly half the runtime.

Further performance enhencements: Use .Value2 instead of .Value property. Or even better, read in the entire columns into an array, work on that in VBA, then write the result back.

YeOldHinnerk
  • 73
  • 8
  • I did this and it made it easier, and I'm not sure when I started the run but it barely got through any of the code. Is there another way I to achieve my goal. – Jeff Jan 30 '20 at 01:20
  • You maybe able to further increase performance by reading the entire range into a 2-dim array, work on that and only write back when you are completely done. In general that should be faster than having to read in so many cells one by one. – YeOldHinnerk Feb 18 '20 at 11:19