Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

fosborb: Dec 15, 2006; Chronic Good Poster

Schedule a repeating meeting in Outlook and trigger VBA off a combination of Category and Subject for your taskscheduler functionality! :v:

# ? Aug 27, 2015 06:17

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 00:21

Hughmoris: Apr 21, 2007; Let's go to the abyss!

fosborb posted:

Schedule a repeating meeting in Outlook and trigger VBA off a combination of Category and Subject for your taskscheduler functionality!

My work uses Lotus Notes. :suicide:

# ? Aug 27, 2015 16:01

melon cat: Jan 21, 2010; Nap Ghost

Yay! This thread is still active. Nice. I'm having a bit of a VB coding issue with my Userform.

I created a Userform to track some tech issues. It works great, but one feature it needs is automatically generated incident numbers.

Screenshot

I currently have it set up so a form pops up, asks for a bunch of incident details, then populates it into the worksheet once a 'Submit' button is pressed. That works just fine. But I'd like to set it up so a number (ranging from 0001-5000) is sequentially generated and inserted into the cell beneath the 'Issue No.' header every time a user clicks the 'Submit' button.

Any suggestions? I've tried most of the solutions that I've been able to find online, but I can't quite get it to work properly. Here's my code for the UserForm

code:

Private Sub SubmitButton_Click()


Dim logRow As Long


Worksheets("Issue Tracker").Activate
' This tells Excel to look at the very last row, move upwards,
' stop at the very top, then put the data 1 row below where
' data was last found
logRow = Worksheets("Issue Tracker").Range("A1048576").End(xlUp).Row + 1


' This is for the sequential numbering
Worksheets("Issue Tracker").Value = Worksheets("Issue Tracker").Value + 1


' The next lines insert the form data into the appropriate
' cell columns, ie. SubmitterBox goes into the 1st column,
' IssueBox goes into the 2nd column, etc.
Cells(logRow, 2) = SubmitterBox.Value
Cells(logRow, 3) = IssueBox.Value
Cells(logRow, 4) = UrgencyBox.Value
Cells(logRow, 5) = OccurenceDate.Value
Cells(logRow, 6) = ResDate.Value
Cells(logRow, 7) = StatusBox.Value
Cells(logRow, 9) = TechnicianBox.Value
Cells(logRow, 10) = ConfirmationBox.Value
Cells(logRow, 11) = NotesBox.Value


' This last line places the curser on the first cell each time
' a new log entry is created
Cells(logRow, 1).Activate


Unload PrinterLogForm


End Sub

EDIT: A second question (if I may): my UserForm also has a combobox with 5 dropdown options. I want to set it up so even if the user leaves it empty, some sort of default text gets entered into the form. Even if it's placeholder text. Either that, or make the Userform Combobox mandatory. What's the best way to do this?

melon cat fucked around with this message at 02:43 on Aug 28, 2015

# ? Aug 28, 2015 00:45

fosborb: Dec 15, 2006; Chronic Good Poster

Hughmoris posted:

My work uses Lotus Notes.

So this is really embarrassing but if you're using Lotus Notes there are several registered DLLs you can include in your VBA to automate data pulls and other interactions.

:negative:

# ? Aug 28, 2015 05:33

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

melon cat posted:

EDIT: A second question (if I may): my UserForm also has a combobox with 5 dropdown options. I want to set it up so even if the user leaves it empty, some sort of default text gets entered into the form. Even if it's placeholder text. Either that, or make the Userform Combobox mandatory. What's the best way to do this?

Ignoring the main question because I'd need to be in front of excel to be certain my suggestion would work, the second question is change this line:

code:


   Cells(logRow, 2) = SubmitterBox.Value

to something like this (I think)

code:


   Submittervalue = if SubmitterBox.Value = "" then
   "Default text", SubmitterBox.Value
    end if

Alternatively to make it mandatory I generally just make one of the first lines of code:

code:


If SubmitterBox.Value = "" then
   Msgbox "Please provide a submitter value, biatch", vbcritical, "Missing information alert"
   Exit sub
End if

# ? Aug 28, 2015 08:23

Hughmoris: Apr 21, 2007; Let's go to the abyss!

*Ignore, figured out the problem.

Hughmoris fucked around with this message at 05:04 on Aug 29, 2015

# ? Aug 29, 2015 04:40

fosborb: Dec 15, 2006; Chronic Good Poster

Hughmoris posted:

VBA is kicking my butt just trying to accomplish basic tasks. I picked up the basics of Python and Perl pretty easily but I'm having a hard time trying to manipulate Excel using VBA.

here, try this:

Writing Excel Macros with VBA, 2nd Edition https://www.amazon.com/dp/0596003595/ref=cm_sw_r_awd_s7s4vbSHRTS2B

# ? Aug 29, 2015 05:04

Hughmoris: Apr 21, 2007; Let's go to the abyss!

fosborb posted:

here, try this:

Writing Excel Macros with VBA, 2nd Edition https://www.amazon.com/dp/0596003595/ref=cm_sw_r_awd_s7s4vbSHRTS2B

Thanks for this. I'll see if I can get my hands on it.

In the meantime, I'm getting tripped up. If I run this code with worksheets("ALERTEXTRACT") selected in the background, it works fine. If I run it with Worksheets("Sheet1") selected, it gives me the runtime error '1004': Method 'Range' of Object'_worksheet' failed. Why does it toss me that error even though I'm explicitly naming what worksheets I want to read from?

Also, is this code considered "good practice" for iterating over a column of unknown length??

Visual Basic .NET code:

Sub GetPhysicianConsult()
    Dim i As Long, j As Long
    Dim wb As Workbook
    Dim rs As Worksheet, ws As Worksheet
    
    
    Set wb = Workbooks("ALERTEXTRACT.txt")   
    Set rs = wb.Worksheets("ALERTEXTRACT")   'rs is the sheet i'm reading from
    Set ws = wb.Worksheets("Sheet1")  'ws is the sheet i'm writing results to
    
    j = 1
    
    For i = 1 To rs.Range("A1", Range("A1").End(xlDown)).Rows.Count
        If rs.Cells(i, 17).Value = "PHYSICIAN CONSULT" Then
            ws.Cells(j, 1).Value = rs.Cells(i, 1)   'writes out the account number associated with the consult.
            j = j + 1
        End If
    Next i
End Sub

*Just read an article saying its better to use a filter, instead of looping. I'll read up on that technique.

Hughmoris fucked around with this message at 01:42 on Aug 30, 2015

# ? Aug 30, 2015 01:22

fosborb: Dec 15, 2006; Chronic Good Poster

This code will avoid the active sheet issue:

code:

For i = 1 To rs.Cells(rs.Rows.Count, "A").End(xlUp).Row

AutoFilter is going to work faster than testing each cell in the range in VBA. My guess is this is because native Excel functions can multithread, but your VBA code can't (without some insane trickery or writing your own libraries)

Other tips:
Use Application.ScreenUpdating = False to avoid draw cycles eating into your execution time.
use Application.Calculation = xlClaculationManual for the same reason.
Write your output to an array and then write the entire array to a output range. This is a huge savings, but requires some fuckery with rediming and transposing arrays.

This is pretty hacky and needs to be cleaned up to match your own code (I didn't worry about offseting for the physician code, etc) but it should get you most of the way there. My parameters were just to test a variety of result sets. The CTimer class was for benchmarking too. You'll certainly want to replace the UsedRange function with something better -- it was reliable for a proof of concept but is unreliable in real life.

Visual Basic .NET code:

Public Sub GetPhysicianConsult(TestWorksheet As String, ColumnOffset As Integer)
    
    FindTimer.StartCounter
    
    Dim i As Long, j As Long
    Dim wb As Workbook
    Dim rs As Worksheet, ws As Worksheet
    Dim rngUsed As Range
    Dim rngCell As Range
    
    Dim ResultsArray() As Variant
    Dim TransposedArray() As Variant 'necessary to handle 100,000+ lines
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set wb = ThisWorkbook
    Set rs = wb.Worksheets(TestWorksheet)   'rs is the sheet i'm reading from
    Set ws = wb.Worksheets("OutputSheet")  'ws is the sheet i'm writing results to
    
    Set rngUsed = rs.UsedRange.Offset(ColumnOffset:=ColumnOffset - 1).Resize(ColumnSize:=1)
    ReDim ResultsArray(1 To 1) As Variant
    
    rngUsed.AutoFilter field:=1, Criteria1:="PHYSICIAN CONSULT"
    
    For Each rngCell In rngUsed.SpecialCells(xlCellTypeVisible)
        ReDim Preserve ResultsArray(1 To UBound(ResultsArray) + 1)
        ResultsArray(UBound(ResultsArray, 1)) = rngCell.Value
    Next rngCell
    
    ReDim TransposedArray(1 To UBound(ResultsArray), 1 To 1)
    For i = 1 To UBound(ResultsArray)
        TransposedArray(i, 1) = ResultsArray(i)
    Next i

    ws.Range("A1").Resize(RowSize:=UBound(ResultsArray)) = TransposedArray
    
    Debug.Print FindTimer.TimeElapsed
    
    
    rngUsed.AutoFilter
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

I benchmarked the above against your original code with a variety of sample sizes and frequency of "PHYSICIAN CONSULT" in the dataset:

code:

Sample Size	Hit Frequency	Original Code (ms)	New Code (ms)
     5,000                20%                   51                 15
     5,000                50%                  108                 18
     5,000                80%                  160                 24
    50,000                20%                  528                108
    50,000                50%                1,085                178
    50,000                80%                1,633                254
   500,000                20%                5,251              1,100
   500,000                50%               10,917              2,000
   500,000                80%               16,881              4,000

# ? Aug 30, 2015 03:30

fosborb: Dec 15, 2006; Chronic Good Poster

fosborb posted:

here, try this:

Writing Excel Macros with VBA, 2nd Edition https://www.amazon.com/dp/0596003595/ref=cm_sw_r_awd_s7s4vbSHRTS2B

I should add that this is for Excel 2002. It is still the best laid out reference for Excel VBA object models and methods that I have ever read, but you are going to miss out on several new features like sparklines, slicers, and ribbons.

Personally, by the time I need to start worrying about hooking into slicers with VBA the project is probably way out of scope already.

# ? Aug 30, 2015 03:46

Hughmoris: Apr 21, 2007; Let's go to the abyss!

fosborb posted:

Great VBA stuff...

Wow, thank you for taking the time to write that up. The read file for this project is of unknown length but typically 220k+ rows. I'll put up the final version of the script when I finish it, for critiquing. Also, that benchmarking tool looks pretty handy. I'm curious how performing these actions natively within VBA will compare to my Perl script that accomplishes the same thing.

# ? Aug 30, 2015 04:12

fosborb: Dec 15, 2006; Chronic Good Poster

Here's a simple timer with little overhead.

http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code

# ? Aug 30, 2015 04:16

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

I would also add that depending on your array dimensions and string sizes be careful if you are using 32 bit office. Its pretty easy to bust the memory limit as vba memory management doesnt seem to be that efficient and garbage collection doesn't always seem to work correctly.

If you use the code you posted taking the last cell in the column and doing xlUp allows you to account for any blank rows.

If you're really getting into writing a lot of complex vba (and don't end up hating its limitations and writing it all in a library instead) make a few "I always use this" classes or modules. Error handling, app initialization, 'everything off' 'everything on' methods (screen updating etc), unfilter all, adding context menu buttons and so on that you can just import into each new project. Always nice to save time.

# ? Aug 30, 2015 09:48

fosborb: Dec 15, 2006; Chronic Good Poster

Hughmoris posted:

I'm curious how performing these actions natively within VBA will compare to my Perl script that accomplishes the same thing.

Your read file is pipe-delimited text, not an xls/xlsx, right? I'd expect Perl to smash the gently caress out of VBA. A regex looking for "PHYSICIAN CONSULT" after the 16th | and looking back to the beginning of the line to the first | should return exactly what you want. Cycle through that and kick it out to whatever.

VBA is great at enabling the nerd hired into enterprise environments (but not under IT) to do some things that would normally require budget approval and IT controls. Which is why Microsoft tries so drat hard to kill it and also why Microsoft will never be able to kill it. There is almost always a better, easier-to-maintain long term solution to complex VBA, but those solutions almost always require more resources than "call that kid who's good at Excel"

# ? Aug 30, 2015 12:20

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

fosborb posted:

AutoFilter is going to work faster than testing each cell in the range in VBA. My guess is this is because native Excel functions can multithread, but your VBA code can't (without some insane trickery or writing your own libraries)

That's probably part of it, but also Excel's own functionality is compiled code so faster, it probably has more direct access to the data in the cells so can do so faster as well, and it doesn't need to marshall every single cell's value back and forth between Excel and VBA (which is a huge cost, as you've noted).

Also, you probably shouldn't use "Redim Preserve" so much because it's quite costly (copies the whole array). If you can't get a count first (which you can here, actually), start off with a minimum size and increase by doubling or adding a chunk when you reach capacity, not one entry at a time.

fosborb posted:

VBA is great at enabling the nerd hired into enterprise environments (but not under IT) to do some things that would normally require budget approval and IT controls. Which is why Microsoft tries so drat hard to kill it and also why Microsoft will never be able to kill it. There is almost always a better, easier-to-maintain long term solution to complex VBA, but those solutions almost always require more resources than "call that kid who's good at Excel"

Yeah it's a great way for businesses to trade development costs for hidden technical debt. It's especially bad if your business is really small and doesn't have proper IT, and they actually believe that your Excel poo poo pile is totally normal and how things should work.

# ? Aug 30, 2015 16:16

melon cat: Jan 21, 2010; Nap Ghost

headcas3 posted:

Ignoring the main question because I'd need to be in front of excel to be certain my suggestion would work, the second question is change this line:
[SNIP]

Thanks very much for this! I'll give it a go. I'm still trying to get that number generator working, so I'll keep you all updated on that issue if I'm able to solve it.

# ? Aug 30, 2015 18:19

Hughmoris: Apr 21, 2007; Let's go to the abyss!

fosborb posted:

Your read file is pipe-delimited text, not an xls/xlsx, right? I'd expect Perl to smash the gently caress out of VBA. A regex looking for "PHYSICIAN CONSULT" after the 16th | and looking back to the beginning of the line to the first | should return exactly what you want. Cycle through that and kick it out to whatever.

Yeah, Perl flies through the file but the kicker is that I also need to print these sheets to different network printers and VBA seems to handle that like a champ.

Here is where I ended up last night after staying up way too late writing code. This is meat of it:

Visual Basic .NET code:

Sub ConsultFilter()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

'declare variables and populate list of units
    Dim wb As Workbook, rb As Workbook
    Dim ws As Worksheet, rs As Worksheet
    Dim i As Integer, LastRow As Long
    Dim Unit(1 To 4) As String
       
    Unit(1) = "Surgery"
    Unit(2) = "Medical"
    Unit(3) = "Oncology
    Unit(4) = "ICU"
    
    'set variables and find out how many rows there are
    Set rb = Workbooks("ALERTEXTRACT.txt")
    Set rs = rb.Worksheets("ALERTEXTRACT")
    Set wb = Workbooks("ConsultReport.xlsm")
    
    LastRow = rs.Cells(rs.Rows.Count, "A").End(xlUp).Row
    
    'Loop through each Unit, and if it find any physician consults for that Unit, it pastes it to those rows to that Units worksheet
    For i = 1 To 4
        With rs
            With .Range("A1:Z1")
                .AutoFilter
                .AutoFilter Field:=4, Criteria1:=Unit(i)
                .AutoFilter Field:=17, Criteria1:="PHYSICIAN CONSULT"
            End With
		'this Range line is ugly and eats up the most processing time it seems.  It copies the filtered data and pastes it to the Units worksheet
            .Range("A1:A" & LastRow & ",D1:D" & LastRow & ",E1:E" & LastRow & ",G1:G" & LastRow & ",J1:J" & LastRow & ",V1:V" & LastRow & "").SpecialCells(xlCellTypeVisible).Copy _
                Destination:=wb.Worksheets(Unit(i)).Range("A1")
        End With
        wb.Worksheets(Unit(i)).Columns.AutoFit
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

# ? Aug 30, 2015 22:24

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Out of interest have you tried loading into and looping through an array and setting the value of each cell individually? Never tried it at what i assume is (200k x 4) 800k lines but for an order of magnitude lower i believe it can be quicker. Turn off calculations and screen updating.

# ? Sep 1, 2015 05:31

Turkeybone: Dec 9, 2006

Just trying to brainstorm a few different methods to achieve an excel goal here:

For each item (with Type X and Price n) in Range A, find all items in Range B that exactly match Type X and are within 5 dollars of price n.

I have a "dumb" method to do similar operations on a smaller scale which is basically "for each item in Range A, copy range B in full and delete each row which does not match" into separate files. That's ok when I am doing 30 searches into 4000 rows, but this one I am thinking now is going to be 20-30 searches into 20,000 rows. How can I make this more efficient (and not copy 20,000 rows 30 times)? I've done dictionaries in the past but I'm decidedly rusty.

The overarching problem to solve here is finding wines from master list B that would be comparable to wines from little list A.

# ? Sep 2, 2015 19:42

khazar sansculotte: May 14, 2004

A pretty easy way to just get that information in front of you would be to use filters (Filter your Type column by X and your price column using the "Between" option from n-5 to n+5). If you have VBA abilities, you could probably whip up a macro to loop over all of the wines in list A and duplicate the sheet with appropriate filters selected each time. Do you need to do something more once you have your information?

khazar sansculotte fucked around with this message at 20:01 on Sep 2, 2015

# ? Sep 2, 2015 19:59

Turkeybone: Dec 9, 2006

Yeah, filters would be fine if I was looking at one wine at a time, but I ultimately want to present a flat, macroless file to someone less excel savvy, and it would probably be like 20 different wines that I want comparative lists for. So 20,000 rows x 20 wines, even if filtered, is going to be a massive file. VBA is going to be the way, I'm looking for a way that is more elegant than "copy 20,000 lines, delete 19,950 of them, repeat 20 times."

# ? Sep 2, 2015 20:44

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Cast_No_Shadow posted:

Out of interest have you tried loading into and looping through an array and setting the value of each cell individually? Never tried it at what i assume is (200k x 4) 800k lines but for an order of magnitude lower i believe it can be quicker. Turn off calculations and screen updating.

I haven't tried that method yet but I'm going to. Once I have a full working version using my current method, I'm going start over and try different techniques to improve the speed. Right now it takes about 12 seconds to run, but the starting file is roughly (200k x 15) cells.

# ? Sep 2, 2015 23:11

khazar sansculotte: May 14, 2004

Turkeybone posted:

Yeah, filters would be fine if I was looking at one wine at a time, but I ultimately want to present a flat, macroless file to someone less excel savvy, and it would probably be like 20 different wines that I want comparative lists for. So 20,000 rows x 20 wines, even if filtered, is going to be a massive file. VBA is going to be the way, I'm looking for a way that is more elegant than "copy 20,000 lines, delete 19,950 of them, repeat 20 times."

You could just output the results of each filtering operation to a new sheet in a new workbook, no need to carry the macro over. I guess this is not really that much more elegant, it's more "filter 19,950 rows out of 20,000, copy the result, repeat 20 times." The only other VBA solution that comes to mind is putting your 20 wines across the top row and looping through your master list 20 times looking for price and type matches, and then inserting those wine names into successive rows below the wine they're being compared against.

Do you absolutely need to have all 20 outputs in front of your end user at the same time? Or could they pick a wine from a drop down list and then the output updates with the relevant info? If the latter you don't need VBA at all, you could just jazz up a ~~pivot~~ table with some data validation and lookup formulas and end up with something substantially nicer looking than either of my VBA ideas would produce. I can elaborate more if that sounds like it fits your need.

edit: don't need a pivot table, a regular table can do fine

khazar sansculotte fucked around with this message at 19:03 on Sep 3, 2015

# ? Sep 3, 2015 18:27

me your dad: Jul 25, 2006

gently caress my life :suicide:

My boss has asked me to merge two Workbooks together. One workbook as 130 columns, the other 129. Besides the difference in columns, some column headers have different labels. So the merged file will have more than 130 columns.

How would you all tackle this? It seems like it's going to be a miserably manual process but I just can't wrap my head around an easy (or least painful) way of doing it. I've been asked to turn it around by COB tomorrow.

My first approach is to simply begin copying each column one by one. If I find a column that doesn't match oh gently caress this is going to suck.

If it matters,

One of the workbooks is 139 rows
The other is 1,950 rows

Thoughts? I've never had to do anything with so many columns.

# ? Sep 3, 2015 21:53

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Use the consolidate tool.

# ? Sep 3, 2015 21:56

me your dad: Jul 25, 2006

I've looked into a bit but I don't know if Consolidate will to work. It seems like the header data needs to be in the same order for this to work. Other instructions I saw indicate that there can be no blank rows, and I've got a lot of blank rows in my data.

# ? Sep 4, 2015 15:09

schmagekie: Dec 2, 2003

me your dad posted:

I've looked into a bit but I don't know if Consolidate will to work. It seems like the header data needs to be in the same order for this to work. Other instructions I saw indicate that there can be no blank rows, and I've got a lot of blank rows in my data.

Here's what I created to sort column headers... I changed it to rely on fewer functions, so hopefully it still works.
To use: create a sheet named "Master" with the correct column headings in row 1.
Run the code.
Each sheet will now have a corresponding sheet with the name "SheetXXX Sorted"

This requires a reference to the Microsoft Scripting Runtime library.

code:


    Sub SortColumns()
    Dim wkb             As Workbook
    Dim jWks            As Worksheet
    Dim wksMaster       As Worksheet
    Dim wksOutput       As Worksheet
    Dim rCorrect        As Range
    Dim rHeaders        As Range
    Dim dDictOrder      As Scripting.Dictionary
    Dim dDictOrderWrong As Scripting.Dictionary
    Dim jKey            As Variant
    Dim rCell           As Range
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    Set dDictOrder = New Scripting.Dictionary
    Set dDictOrderWrong = New Scripting.Dictionary
    
    Set wkb = ActiveWorkbook
    Set wksMaster = ActiveWorkbook.Sheets("Master")
    Set rCorrect = Intersect(wksMaster.Range("1:1"), wksMaster.UsedRange)

    'setup dictionary with correct order
    On Error Resume Next
        For Each rCell In rCorrect
            With dDictOrder
                .Add rCell.Value2, rCell.Column
            End With
        Next rCell
    On Error GoTo 0
        
    'iterate sheets
    For Each jWks In ActiveWorkbook.Worksheets
        If Not jWks Is wksMaster Then
            dDictOrderWrong.RemoveAll
            Set rHeaders = Intersect(jWks.Range("1:1"), jWks.UsedRange)
            For Each rCell In rHeaders
            
                If IsEmpty(rCell) Then
                    'if header is missing, give it a name
                    rCell.Value2 = "Field " & rCell.Column
                End If
                
                On Error Resume Next
                    With dDictOrderWrong
                        .Add rCell.Value2, rCell.Column
                    End With
                On Error GoTo 0
                
            Next rCell
                
            Set wksOutput = wkb.Sheets.Add
            ActiveSheet.Name = jWks.Name & " Sorted"
            
            For Each jKey In dDictOrderWrong.Keys
            
                If dDictOrder.Exists(jKey) Then
                    'copy column of jKey (item) to column of order(item)
                    jWks.Columns(dDictOrderWrong.Item(jKey)).Copy _
                        wksOutput.Columns(dDictOrder.Item(jKey))
                Else
                    'copy to end of destination
                    jWks.Columns(dDictOrderWrong.Item(jKey)).Copy _
                        wksOutput.Columns(WorksheetFunction.Max(FindEmptyColumn(wksOutput), dDictOrder.Count + 1))
                End If
                
            Next jKey
            
            'color it
            For Each rCell In wksOutput.Range(Cells(1, 1), Cells(1, FindEmptyColumn(wksOutput) - 1))
                If dDictOrder.Exists(rCell.Value2) Then
                    rCell.Interior.Color = vbGreen
                Else
                    'make it red and populate correct header
                    rCell.Interior.Color = vbRed
                    If rCell.Column <= dDictOrder.Count Then
                        rCell.Value2 = wksMaster.Cells(1, rCell.Column).Value2
                    End If
                End If
            Next rCell
            
        End If  'not order sheet
    Next jWks

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

    End Sub

    Public Function FindEmptyColumn(wks As Worksheet) As Long
    On Error Resume Next
        'ResetUsedRanges
        FindEmptyColumn = wks.Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column + 1
    On Error GoTo 0

    FindEmptyColumn = WorksheetFunction.Max(FindEmptyColumn, 1)
    
    End Function

# ? Sep 4, 2015 15:15

Turkeybone: Dec 9, 2006

Ronald McReagan posted:

You could just output the results of each filtering operation to a new sheet in a new workbook, no need to carry the macro over. I guess this is not really that much more elegant, it's more "filter 19,950 rows out of 20,000, copy the result, repeat 20 times." The only other VBA solution that comes to mind is putting your 20 wines across the top row and looping through your master list 20 times looking for price and type matches, and then inserting those wine names into successive rows below the wine they're being compared against.

Do you absolutely need to have all 20 outputs in front of your end user at the same time? Or could they pick a wine from a drop down list and then the output updates with the relevant info? If the latter you don't need VBA at all, you could just jazz up a ~~pivot~~ table with some data validation and lookup formulas and end up with something substantially nicer looking than either of my VBA ideas would produce. I can elaborate more if that sounds like it fits your need.

edit: don't need a pivot table, a regular table can do fine

Hmm.. yeah I am usually of the mindset to code through but honestly the drop-down is probably more useful for the end user. Or the other thing is that ultimately, even if "filter 19950 out of 20,000" is ugly, I'm only going to do it once. So I maybe I should just suck it up and do it that way, and then go spend those ten minutes making a coffee or something.

# ? Sep 4, 2015 16:00

me your dad: Jul 25, 2006

schmagekie posted:

Here's what I created to sort column headers... I changed it to rely on fewer functions, so hopefully it still works.
To use: create a sheet named "Master" with the correct column headings in row 1.
Run the code.
Each sheet will now have a corresponding sheet with the name "SheetXXX Sorted"

This requires a reference to the Microsoft Scripting Runtime library.
code...

Thanks. I really appreciate it. I've decided to just do it manually. This is going to be a nightmare either way and I've settled into accepting this will be my day. This really falls outside of my normal job too, which adds insult to a poo poo situation.

# ? Sep 4, 2015 16:12

schmagekie: Dec 2, 2003

me your dad posted:

Thanks. I really appreciate it. I've decided to just do it manually. This is going to be a nightmare either way and I've settled into accepting this will be my day. This really falls outside of my normal job too, which adds insult to a poo poo situation.

Good luck! I give up after about 10 columns, hence writing that.

# ? Sep 4, 2015 16:26

chippy: Aug 16, 2006; OK I DON'T GET IT

I've got an excel sheet with a few columns of values in. I'd like to use the values to lookup other values in an SQL database and populate other columns in the sheet for them.

Simple example:

Column A contains integers corresponding to the ID column in a table of Foos in my database, I'd like to lookup their names in the database using the ID, and fill the names in in column B.

Is this do-able?

p.s. I tried loading in the entire table in a seperate sheet and just doing lookups in it, but there are too many rows.

# ? Sep 4, 2015 17:37

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I have a big list of string values and want to categorize them. My idea is make a list of keywords to search each string for, and go from there.

Colors Fruits
Red Apple
Blue Pear
Gray Melon
Pink Pepper

So if any of those 'colors' are in the string, the category becomes 'Colors', but if the substring 'Pear' is found, the category becomes 'Fruits'. What do I need to learn to do? The lookup and the substring thing, right?

I want to do it this way instead of hammering out some massive compound if/else formula.

# ? Sep 9, 2015 20:11

Hughmoris: Apr 21, 2007; Let's go to the abyss!

I'm a bit stumped on a problem.

How do I execute a macro after a user clicks the Refresh All button? I have a fully functioning macro intended but I'd like to have it key off the action of Refresh All, instead of the user having to manually launch the macro. My googling is not turning up any examples I can get working.

*Disregard. I got it working. Here's some info I used incase anyone needs to reference it in the future:
https://excelandaccess.wordpress.com/2014/01/18/create-beforeafter-query-update-events/

Hughmoris fucked around with this message at 01:43 on Sep 16, 2015

# ? Sep 16, 2015 00:21

me your dad: Jul 25, 2006

I need to populate a column with seemingly random time stamps. I would need to establish a range for the dates - something like, between October 14, 2014 and the current date.

The time stamp would need to be in this format: 9/5/2015 16:08

No time stamp should be the same.

Is this possible?

# ? Sep 17, 2015 14:32

fosborb: Dec 15, 2006; Chronic Good Poster

=RANDBETWEEN(4192600000,NOW()*100000)/100000

There is a nonzero chance of a duplicate. 0.1% in 50,000 generations.

To avoid duplicates, you could generate your list, copy/paste as values, then use Data > Remove Duplicates

fosborb fucked around with this message at 14:53 on Sep 17, 2015

# ? Sep 17, 2015 14:49

me your dad: Jul 25, 2006

fosborb posted:

=RANDBETWEEN(4192600000,NOW()*100000)/100000

There is a nonzero chance of a duplicate. 0.1% in 50,000 generations.

To avoid duplicates, you could generate your list, copy/paste as values, then use Data > Remove Duplicates

Thank you! I entered it and then formatted the cells to date and time and it worked great.

Can you please tell me how this works? For example, what is 4192600000? What are the other numbers?

# ? Sep 17, 2015 15:02

fosborb: Dec 15, 2006; Chronic Good Poster

Excel records dates as a number of days since 1/0/1900 and records times as a fraction of a day. 0.000001 is about 1 second. (It's actually 1/86400 but close enough for this) So 1.000001 is 1/1/1900 12:00:01 AM.

Randbetween only does whole numbers, so we multiply the date.time values by 100000, get the random number, and then divide by the same to convert back to date.time.

This is really useful knowledge if you work with times a lot in excel (and also how you can get charts of time into whole minute increments) but it's completely useless in every other area of your life because no other program records datetime in this dumb way.

# ? Sep 17, 2015 19:11

pad thai hi-five: Aug 11, 2003; Oven Wrangler

A client has asked me whether it is possible in an Excel macro to specify a user account with which to print. I have never done macros, can be considered clueless.
He has an Excel file that should only print out to a selected printer (that printer being loaded with the very special paper to be used). He's limited the available printers to that one.
Now he wants to make sure that that very special paper does not get used otherwise, without impacting daily work of the users, who should have a all the other, normal, printers available.
So I came up with the idea of limiting usage of that specific printer to a certain domain user account.
Which leads to the question above. Is it possible? Would it mean account credentials stored in the macro?

# ? Sep 18, 2015 18:41

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

You can grab username from windows pretty easily. Millions of examples online. Then its just if username = x use this printer? Unsure on what exactly your trying to do from there?

# ? Sep 18, 2015 21:52

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 00:21

pad thai hi-five: Aug 11, 2003; Oven Wrangler

The idea would be to not grab the user of the current session, but use a special user account that is exclusively allowed (on the print server) to use the special printer.

# ? Sep 18, 2015 22:40

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »