Jump to content

Excel & VBA coding


forteh

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 by forteh
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...