Posts by browncoat

Thanks, I ended up just created a routine to replace the commas with semicolons before copying into the sheet

I've been working on this workbook for about a year. Most of the functionality is done but I have issues with random crashes and occasional subroutines that get triggered for no apparent reason, i.e. I will be following along the code as it runs, line by line, but it will randomly jump to some other subroutine and run through it, even after I've gone through and removed every instance it is called.

Additionally, the entire workbook seems to crash randomly when manipulating tables but it is inconsistent and not easy to replicate -- sometimes it happens, most of the time it doesn't. Not sure what to do at this point.

Actually I just realized I can just use SUMIF

I have a table and I want to get the value of a cell based on multiple criteria. Here is my currently formula

=([@[Spec Mean]]-((XLOOKUP("BSL",[v.Con],[Spec Mean]))))/ (XLOOKUP("BSL",[v.Con],[Spec Mean]))

The XLOOKUP function is returning the value for the ROW with "BSL" in the [v.Con] column but I have instances where there are multiple rows in that column with "BSL" and I need a second column to further specific the correct row. How do I include a second column requirement?

Thanks

I am importing records into excel by iterating through the field names and values and inserting into a table. Fields are strings, numbers, and dates. The problem is that I have a list of values that are comma separated and I want to insert them into a table as a string but excel keeps converting the cell to number, removing all the commas, and turning the string into one very large number. How can I prevent excel from doing this without affecting other cell formatting for fields that need to be number or date?

Oh that's a good idea, thanks!!

Ok good to know thanks. Is there an optimal/most efficient way to do this? I need to pull the data from the access database but loading it into a spreadsheet first seems like it would be inefficient. Would iterating through the results from the query and doing additem be better?

Still haven't figured this out. Any help is greatly appreciated

Still haven't figured this out. Any help is greatly appreciated

Oh good to know, thanks, I've switched it to .Column. How is this different/more efficient, out of curiosity?

for the combobox, boundcolumn is 2, textcolumn is 1. columncount is 2

Public Sub UpdateChemList(DBconnect As Boolean)
Dim varr As Variant
Dim sqlstr As String
Dim table As String, recordlabel As String, returnvalue As String, recordid As String

If SetupForm.radioligandsradio = True Then
sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID WHERE (((Inventory.Retired)=False) AND (Not (Chemicals.Radionuclide)=""""))"
    Else
    
    If SetupForm.allsamplesradio = True Then
    sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID"
    Else

        If SetupForm.activesamplesradio = True Then
        sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID WHERE (((Inventory.Retired)=False))"
        Else
        sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID"
        End If
    End If
End If

If SetupForm.chemsortname = True Then
 sqlstr = sqlstr & " ORDER BY Chemicals.Compound;"
Else
    
    If SetupForm.chemsortrecent = True Then
     sqlstr = sqlstr & " ORDER BY Inventory.SampleID DESC;"
    Else
     sqlstr = sqlstr & ";"
    End If
End If



varr = SQLBE(sqlstr) ' custom function below

SetupForm.ChemList.Column = varr

End Sub

this is the function I use to get results from the database

Public Function SQLBE(sqlstr As String) As Variant
Dim MyDB As New Connection, MyRS As Recordset
Dim vArray As Variant
Dim i As Integer

Set MyDB = New ADODB.Connection
MyDB.Open (strcon)
Set MyRS = New ADODB.Recordset
With MyRS
.CursorLocation = adUseClient

.Open sqlstr, MyDB, adOpenDynamic, adLockReadOnly

If Not (MyRS.BOF And MyRS.EOF) Then

vArray = .GetRows

If .RecordCount = 0 Then
 SQLBE = "no record" ' No records; abort processing
Else
 
  SQLBE = vArray

End If
End If

.Close
End With

End Function

this is simplified for brevity but is the gist of what the code does that causes the problem. This worked find when I used a named range to populate the combobox.

Public Sub ChangeChemList()
Dim sampleid as Integer
sampleid = 337

SetupForm.ChemList.value = sampleid

End Sub

So if the item in the list has the id 337 and I do msgbox(SetupForm.Chemist.Value) it will display 337. But if I try to select this option by setting the value to 337 I get that error.

A activex combobox on a userform has been working with two columns, with the first column showing an item name and the second displaying the item code. The second column is set as bound and the item code is used elsewhere in the form. I populated it by setting the .RowSource = tablerange and the combobox displays the first column when an item is selected but the code in the bound column is what sets the value.

I recently switched to populating it with a SQL query to an Access database rather than from a table in the excel file and use .List = Application.Transpose(var) using SQL query results , but this has caused it to stop working. The combobox populates fine but I can't select a value from VBA code. Previously, I could make one of the options become selected in the combobox by setting the value of the combobox (e.g. Combobox.Value = 123) to a number that was listed in the bound column (2) and it worked fine. Now, it gives me a run time error '380' "could not set the value property. Invalid property rule". However, if I select the item in the combobox and have the combobox.value returned (e.g., msgbox(Combobox.value)), it shows the value I am trying to select. So the value is there but for some reason I can't select the item using the code with VBA. I've tried changing the variable type of the value I'm trying to set to the combobo to variant and integer but still the same problem. Help is greatly appreciated!