Excel & VBA coding

[forteh]

Anyone here any good at VBA coding in excel?

I'm trying to get a spreadsheet to search up and down a column until it recognises a specific trigger above and below it (by conditional formatting or a specific cell value) and then multiplying a set of cells within the range by the top most cell.

Really badly explained I know but can supply sample spreadsheets if anyone fancies playing with it. I don't think it's possible to do it with standard excel functions hence the need for VBA, however if it can be done all the better.

Loaf of stale bread and a chocolate hobnob to anyone that can help

TL: DR I don't know how to code VBA and want someone to help!

[Luke Rainbird]

Haven't done much in the way of VBA but have played around with Excel a fair bit over the years. Fire a copy across and I'll take a look tonight (if the laptop parts arrive today...)

[forteh]

Be sweet if you could have a look to work out if I can do what I want to do

I've attached a test sample bill of materials as exported directly from solidworks.  The problem I have is that the solidworks BoM does not total up the quantities of component parts within a subassembly which is a massive failing if you ask me.  I want to export the BoM to excel and use macros to format the file and automatically calculate the correct total quantities.

Eventially I want to achieve the following: -

• Highlight the rows containing assemblies (rows 1, 8 & 19) to form an Assembly Header - I can already do this via conditional formatting.
• For each subsequent cell after an Assembly Header I want to multiply the quantity by the value listed on the Assembly Header itself.  I want to carry out the same routine throughout the sheet. Using the attached sheet as an example I essentially want all the component qty cells [D9:D18] within TEST ASSEMBLY 3 to be multiplied by the assembly qty cell [D8].

Any sub-assemblies should be likewise calculated through.

Additional columns/rows can be created if need be (they can be hidden/deleted at a later time for the final document), likewise the PT column can be hacked around with as it will eventually be hidden and is only used for reference.

Test Assembly.xlsx

[Luke Rainbird]

Assuming computer bits arrive, I'll try and take a look this evening for you mate. Currently knee deep in laying a large concrete slab so only skim read the above post but will holler if I need any clarification

[forteh]

Asked the same question on the mrexcel forums and a kind user from Indiana a small chunk of code that worked perfectly for my example file. However it didn't work quite properly on a live file due to the way he'd identified the assemblies, a minor code change and it's all good.

10 lines of code to do what I've been trying to achieve for months with standard excel functions!

[Luke Rainbird]

Ah brilliant, glad you got it working. Fancy sharing the code out of interest?

[forteh]

The initial code was looking for the text "ASSEMBLY" in the description field and running from there: -

Code:

Sub AssemblyValue()
    Dim r As Range
    Dim AssemblyValue As Double
    
    Application.ScreenUpdating = False
    For Each r In Range("D3", Cells(Rows.Count, 4).End(xlUp))
        If InStr(UCase(r.Offset(, -2)), "ASSEMBLY") > 0 Then
            AssemblyValue = r
        Else
            r.Offset(, 1) = r * AssemblyValue
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

 

I suggested that identifying the assemblies by the blank material and finish reference fields was a more robust method and he changed it to this: -

Code:

Sub AssemblyValue()
    Dim r As Range
    Dim AssemblyValue As Double
    
    Application.ScreenUpdating = False
    For Each r In Range("D3", Cells(Rows.Count, 4).End(xlUp))
        If r.Offset(, 4) = "" And r.Offset(, 5) = "" Then
            AssemblyValue = r
        Else
            r.Offset(, 1) = r * AssemblyValue
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

 

I've not had chance to test the modified code yet but I don't see why it shouldn't work

He also recommended and pointed me in the direction of this...

 

Edited March 15, 2017 by forteh

[Luke Rainbird]

Excellent. Having not really played around with the VBA side of things much it's always nice to get an insight into how folk in the know approach things