Posts by TheGlovner

Re: ADO Crash on successive calls to SQL Server

I had a similar issue once where the code altered the users environment inputs (PROD/ACCT/SYST/INTE/UNIT) into the appropriate server name (DSA/DSAA/DSAS/DSAI/DSAU).

The environment was entered byref to the code that altered it to the appropriate server name, so when it made the second call it went through the same code but never had to override say SYST to DSAS as it was already set to DSAS from the previous SQL call.

I've no idea why it was failing, or why this fixed it, but all I did was pass in a temporary copy of the environment string (i.e SYST), which was overridden to the server name, but wouldn't overwrite the original environment string, so when the second call was made it passed SYST from the input variable into the temp variable which was passed to the code to run the SQL.

EDIT: I should add that in theory changing the passing of the parameter to ByVal should have achieved the same result, but I hadn't written the SQL part of the VBA so didn't have access to alter that code directly, it was also an add-in referenced by a number of sheets so doing it this way avoided the regression testing impact that would have been inherent with changing the SQL VBA Code.

And it worked.

As I say no idea why, but if it sounds similar give it a bash.

Re: Macro to help match and sort data

Try this out.

You'll need to alter some references to suit your data setup.

Module Code:

Option Explicit
'Handles the extraction of data
Public Sub ExtractSampleList()
Dim rngInput    As Range
Dim rngSmpls    As Range
Dim strCharCode As String
Dim varWrkCell  As Variant
Dim colSmpls    As Collection
Dim clsOutputs  As OutputClass
'Get the input and set the search range
With wsInputSheet
    Set rngInput = .Range("SelectionInput")
    Set rngSmpls = .Range("SampleList")
End With
'Get the Single Letter Character Code
strCharCode = Left(rngInput.Value2, 1)


'Search the range where the samples are located
Set colSmpls = New Collection
For Each varWrkCell In rngSmpls
    'If the first character of the sample equals the single character code then extract the data
    If Left(varWrkCell.Value2, 1) = strCharCode Then
        'Set up new instance of class and construct it
        Set clsOutputs = New OutputClass
        Call clsOutputs.Construct(varWrkCell)
        
        'Add class instance to collection
        colSmpls.Add clsOutputs
    End If
Next varWrkCell
'Call the output routine (passing the collection of objects extracted
Call OutputData(colSmpls)
'Clear up objects
Set rngInput = Nothing
Set rngSmpls = Nothing
Set varWrkCell = Nothing
Set colSmpls = Nothing
Set clsOutputs = Nothing
End Sub
Private Sub OutputData(ByRef colSmpls As Collection)
Dim lngOutputRow    As Long: lngOutputRow = 1 'Assuming Header in row 1 so this will move to row 2 on entering the loop
Dim lngChrCdCol     As Long: lngChrCdCol = 7
Dim lngSmplCol      As Long: lngSmplCol = 8
Dim clsOutputs      As OutputClass
'Loop round each object in the collection and output
With wsOutputSheet
    For Each clsOutputs In colSmpls
        lngOutputRow = lngOutputRow + 1
        .Cells(lngOutputRow, lngChrCdCol) = clsOutputs.CharacterCode
        .Cells(lngOutputRow, lngSmplCol) = clsOutputs.CharacterCode & " " & clsOutputs.SampleData
    Next clsOutputs
End With
End Sub

Class Module Code:

Option Explicit
Private pStr_SampleData As String
Private pStr_CharCode   As String
Public Sub Construct(ByRef varInput As Variant)
Dim varSplitString  As Variant
varSplitString = Split(CStr(varInput.Value2), " ", 2)
pStr_CharCode = varSplitString(0)
pStr_SampleData = varSplitString(1)
End Sub
Public Property Get CharacterCode() As String
    CharacterCode = pStr_CharCode
End Property
Public Property Get SampleData() As String
    SampleData = pStr_SampleData
End Property

Class Module was also reamed to OutputClass.

Re: Auto fill unknown values from available data: day, month and year.

Cool, if you need any more help obviously just say, I'm subscribed to this post now anyway.

Re: VBA Code for renaming worksheets

So basically you want to rename SheetName to be [SheetName & ValueFromColB] when some sort of flag is set in Column E?

So what is column E being set to in order to prompt the change to be made?

Then it's just the case of evaluating that value with an if statement which encapsulates the code that is used to rename the sheet, so something like:

If ValueInColumnE = TheValueThatPromptsTheNameChange then
        'Code To Update Sheet Name In Here
End If

Re: Divide rows into multiple rows if a cell has more than 1 "row"

Can't download the sheet, at work waiting for some tests to run.

Something like this should help you though:

'Takes the input string and splits it into an array using a Line Feed as the delimiter
Public Function SplitLineBreaks(ByRef strInput as String) As Variant
    SplitLineBreaks = Split(strInput, vbLf)
End Function

So you need to feed in the value from the cell as the strInput and it will return an array with a minimum size of 0 to 0, or 0 to X based on the number of line breaks found in the string, with each element representing the string from the start/line break to the next line break/end.

Re: Auto fill unknown values from available data: day, month and year.

So I've taken a bash at solving your problem.

I don't think the answer is to try and use code to solve it, seems like over engineering to me.

To get round the idea of overtyping formulas and having to use code to address the formula being lost I would strip out the Input and keep this separate from the calculation.

So in the above example you have an input area (Columns B, C & D) and a calculation area (Columns F, G & H).

If you enter a yearly value, this is used to populate Column H, calculate Column F (Input / 52) and calculate Column G (Input / 12)
If you enter a weekly value, this is used to populate Column F, calculate Column H (Input * 52), then Column H is used to calculate Column G (Yearly / 12)
If you enter a monthly value, this is used to populate Column G, calculate Column H (Input * 12), then Column H is used to calculate Column F (Yearly / 52)

This way you don't have to worry about overtyping or replacing formula.

Usually good practice to separate your Inputs from your working.

Re: Auto fill unknown values from available data: day, month and year.

[TABLE="width: 500"]

Row\Column

A

B

C

D

E

F

G

H

1

[TABLE="width: 108"]

WeeklyInput

[/TABLE]

MonthlyInput

YearlyInput

WeeklyCalc

MonthlyCalc

YearlyCalc

2

Category

=IFERROR(IF(LEN($B2)>0, $B2, $I2/52), "")

=IFERROR(IF(LEN($C2)>0, $C2, $I2/12), "")

=IF(LEN($D2)>0, $D2, IF(LEN($C2)>0, $C2*12, IF(LEN($B2)>0, $B2*52, "")))

[/TABLE]

Re: Auto fill unknown values from available data: day, month and year.

Are you using the year to calculate both Month & Weeks?

i.e.

YearlyTotalFromMonth = MonthlyTotal * 12
YearlyTotalFromWeeky = WeeklyTotal * 52
MonthlyTotal = YearlyTotal / 12
WeeklyTotal = YearlyTotal /52

So if someone input a monthly total it would calculate the yearly total and then use this result to calculate the weekly total?

Re: How to Automatically remove blank rows when selecting from a list (DV)

Explanation is a touch confusing.

Is your data (Hotel, Mall & Bank List) on different worksheets? I'm assuming it must be since you quote the range as being in column A in each case so they would all overlap on the same sheet.

If they are different worksheets, then that code is only designed to work based on a change to your current worksheet. If you wanted to affect the visibility of rows on other worksheets you would have to reference them explicitly.

Re: macro - to create new sheets from a data validation list

This should create the sheets and name them for you, unclear what you want to do from there, I assume you want some data taken from other sheets and added to them.

Option Explicit
Public Sub CreateCustomerSheets()
Dim varWrkCell  As Variant
 Dim wsTemp      As Worksheet
Dim rngCustomer As Range: Set rngCustomer = wsSourceData.Range("Source_Customer_List")
For Each varWrkCell In rngCustomer
     Set wsTemp = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsTemp.Name = varWrkCell.Value2
Next varWrkCell


'Clean Up Objects After Use
set varWrkCell = Nothing
set wsTemp = Nothing
set rngCustomer = Nothing


End Sub

Couple of objects/references would need renamed in your project for it to work:

"wsSourceData" is the code name I've given to the sheet where my test data was to make sure it worked.
"Source_Customer_List" is the named range on the wsSourceData worksheet where my test Customer Names were held.

Hopefully that gets you started.

Re: Change color of final result based on raw data given

Another option is of course using Styles rather than fill colours which incorporate a number of properties into a single command, where if you just change the fill colour your font colour may not show too well against the new background colour.

If you want to use styles rather than background fill colour the property for the range is just ".style". The value is a string representation of the style name.

Re: Change color of final result based on raw data given

Can't download the sheet as I'm at work.

But you should be able to use the ".Interior.Color" property of the range/cell to get the fill colour of the cell.

Not sure what the shade of red is that you want to validate against but you can easily get this by following your code through to where it picks up the cell colour.

Once you know the serial number for the colour you should easily be able to validate an If Statement against it.

Something like:

If lngOriginalFill = RedColourSerialCode then
     cell.Interior.Color = RedColourSerialCode
end if

Re: macro - to create new sheets from a data validation list

So you have 50 customers in a range (which you use to prompt the dropdown options) and you want to use each customer to create a single sheet per customer.

Is that correct?

Is there are good reason for creating 50 spreadsheets? I'm a little confused on the purpose.

Re: Is this Possible - Select ComboBox value, the give answer

Quote from RBLearning;799566

Thanks, I'm playing around.

Just noticed that you want it displayed in a textbox rather than on sheet. So still a similar solution but something like:

Public Sub DisplayLocationFromDate()Dim dteSelectedDate As Date
Dim strLocation     As String
Dim rngRefData      As Range


Set rngRefData = AreaWhereDateLocationDataSits
dteSelectedDate = ComboBoxCodeAsAFunctionToReturnSelection
strLocation = Application.WorksheetFunction.VLookup(dteSelectedDate, rngRefData, 2, 0)     'I'm assuming that the date is column 1 and the location column 2
MsgBox "Date Selected: " & Format(dteSelectedDate, "DD/MM/YYYY") & vbNewLine & _
       "Location: " & strLocation, vbOKOnly + vbInformation
End Sub

I've just thrown that together on textpad so it may have some bugs but hopefully it gives you an idea.

I'm assuming you have the combobox code already, but this would require it to be set as a function rather than a subroutine so the selected value can be passed back to the dteSelectedDate variable.

Re: Is this Possible - Select ComboBox value, the give answer

Or index(match()) as rather than v/hlookup.

Re: Is this Possible - Select ComboBox value, the give answer

Can you not return the location based on a v/hlookup formula on the sheet but the reference value would be based on a cell that can be populated with the appropriate date following your combobox selection?

Hopefully that makes sense.

Re: Exact Multiply in Excel

I'd guess that your initial result was actually 8.515, but the cell was formatted to only show 2dp. So it rounds it for display (8.515 rounded nearest to 2dp is 8.52) but still retains the full precision 8.515 within the cell.

As Rory pointed out, rounding the original formula actually converts the cell contents to 8.52 rather than just for display purposes.

So, it was always multiplying accurately, it's just you weren't aware of the true input to the multiplication.

Re: Counting colors and updating formula

To expand on what KjBox has already said.

I have a module contained on a stand-alone add-in workbook that I can either reference (for users that have the same drive access), or just pickup and drop into the project I'm working on (for users that don't have access to the same drives).

It contains a number of different functions that I use all the time (GetLastPopulatedRow, GetLastPopulatedColumn, GetUserID etc), but it also has two public subroutines just called ProcessStart and ProcessStop.

The process start basically switches automatic calculations to manual, sets EnableEvents, ScreenUpdating and DisplayWarnings to False.

The process end switches all these things back (I do have a little bit more going on depending on whether I want to force certain application settings or I return the settings to the users application settings as they were pre-processing.

Saves me the hassle of having to do this every time though.

Re: open an excel file and insert a row with data on it then close it back

I'll try and take a look at the issues when I have a moment.

As to how it can help with the writing of the data, basically you would use the add-in to request write access to the file, if it can't give you write access then it would return an error message confirming that write access isn't available at that time (you may want alter the message to confirm to try again in a moment).

My assumption is that it should only be getting opened by this process so people shouldn't be sitting in it for long.

Re: Simpying Repetitive Macro Codes

If the row range changes for each one but the processing on that range stays the same then you should set the range accordingly from the checkbox click routine, then pass this along with the checkbox to the other generic functional routine.