logo
down
shadow

VBA - Using criteria list select and clear or select an replace values


VBA - Using criteria list select and clear or select an replace values

By : Tom
Date : November 22 2020, 07:01 PM
wish helps you What makes you go for a VBA solution ? Hard to do much without a glance of you data and expected result.
Non VBA option:
code :
Option Explicit

Sub update_id()

Dim D1 As Object: Set D1 = CreateObject("scripting.dictionary")
Dim R1 As Range: Set R1 = Range("A2:A32")
Dim R2 As Range: Set R2 = Range("E2:E15")
Dim Rtmp As Range

For Each Rtmp In R2
    D1(Rtmp.Value) = Rtmp.Offset(0, 1).Value
Next Rtmp

For Each Rtmp In R1
    If D1.exists(Rtmp.Value) Then Rtmp.Offset(0, 1) = D1(Rtmp.Value)
Next Rtmp

End Sub


Share : facebook icon twitter icon
Query to select a list of values from a table based on this multiple criteria

Query to select a list of values from a table based on this multiple criteria


By : user3341889
Date : March 29 2020, 07:55 AM
this will help I have a table with 2 columns, Ex_Id and Term_Id, both int type. My table will have many Term Ids for one Exercise Id.
code :
SELECT Ex_ID 
FROM TableName 
WHERE Term_ID IN (?, ?, ?)                --- (2, 3, 4)
GROUP BY Ex_ID
HAVING COUNT(DISTINCT Term_ID) = 3        --- number of terms in the above list
SELECT DISTINCT Ex_ID
FROM TableName e
WHERE NOT EXISTS
        ( SELECT *
          FROM TableName t
          WHERE t.Term_ID IN (?, ?, ?)           --- the list of terms
            AND NOT EXISTS
                  ( SELECT *
                    FROM TableName a
                    WHERE a.Term_ID = t.Term_ID
                      AND a.Ex_ID = e.Ex_ID
                  )
        ) 
SELECT DISTINCT Ex_ID
FROM TableName e
WHERE NOT EXISTS
        ( SELECT *
          FROM
            ( SELECT ? AS Term_ID  
            UNION
              SELECT ?
            UNION 
              SELECT ?
            ) AS t
          WHERE NOT EXISTS
                  ( SELECT *
                    FROM TableName a
                    WHERE a.Term_ID = t.Term_ID
                      AND a.Ex_ID = e.Ex_ID
                  )
        ) 
Clear other text and select field values on main select change

Clear other text and select field values on main select change


By : user2015958
Date : March 29 2020, 07:55 AM
Hope that helps I have a dropdown to change the main category on a form, this subsequently shows and hides the relevant dropdowns to enter data regarding that category. , It's getElementById, not getElementByID:
code :
function optionCheck() {
    document.getElementById("wattage_input").value = '';
    document.getElementById("dimmable_input").value = '';
}
Select returning a collection of true/false values, not a list of objects matching criteria

Select returning a collection of true/false values, not a list of objects matching criteria


By : Jen Broockerd
Date : March 29 2020, 07:55 AM
wish help you to fix your issue What you want is Where extension:
code :
var subset = data.Where(x => x.Name != null);
Select the lower of two values in a table unless some criteria is met, then select the higher value

Select the lower of two values in a table unless some criteria is met, then select the higher value


By : warhammer
Date : March 29 2020, 07:55 AM
hope this fix your issue Just rank those records with dilution_fact=E as a lower rank value(2) and the rest as(1), and after that sort by the dilution_factor as below
code :
with data
  as ( 
SELECT  
    s.task_code,  
    s.sys_loc_code,
    a.analyte_type, 
    a.chemical_name, 
    t.dilution_factor, 
    r.result_text, 
    r.lab_qualifiers
FROM 
    dt_sample s ON f.facility_id = s.facility_id
JOIN 
    dt_test t ON s.sample_id = t.sample_id AND s.facility_id = t.facility_id
JOIN 
    dt_result r ON t.test_id = r.test_id AND t.facility_id = r.facility_id  
JOIN 
    rt_analyte a ON r.cas_rn = a.cas_rn
WHERE 
    s.task_code= 'RFS01-10.1810009'
    AND s.sample_type_code IN ('F','D') 
    AND a.analyte_type IN ('Semivolatile','Volatile') 
    AND s.sys_loc_code IN ('15699', '45608', '90399', '91203', 'MOUND R1-0', '99305', 'MSETINF', '23296')
  )
,ranked_results
  as (
select *
       ,row_number() over(partition by task_code
                                       ,sys_loc_code
                                       ,analyte_type
                                       ,chemical_name
                            order by case when lab_qualifiers<>'E' then 1 else 2
                                     end,dilution_factor) as rnk
  from data
)
select * 
  from ranked_results
  where rnk=1
Select every nth value from list and replace values between them

Select every nth value from list and replace values between them


By : user3052158
Date : March 29 2020, 07:55 AM
should help you out You can store every n'th item in a separate variable, then assign the whole list to the fill value and then fill back the stored ones (this won't create a copy of the original list):
code :
import itertools as it

l = list(range(20))
tmp = l[::5]
l[:] = it.repeat(0, len(l))
l[::5] = tmp
l = list(range(20))
l = [0 if i % 5 else x for i, x in enumerate(l)]
Related Posts Related Posts :
  • vba Loop over a non-contiguous range
  • SSAS 2008/Excel 2007 - Can't see cube
  • Output a NULL cell value in Excel
  • Excel VBA Clear formats when value is 0
  • Value as hyperlink?
  • Adding references from VBA Causing Password Prompt To Appear
  • keep getting "Next without for" while I start with for
  • How to color 56 cells in B column
  • Excel from web scraping
  • Nested IF statements in Excel [Over the 7 allowed limit]
  • How can I copy data from one sheet to another sheet in Excel 2007 using a macro?
  • Return a value of '1' a referenced cell is empty
  • How to create and update spreadsheet using OLEDB in C#?
  • Excel turning my numbers to floats
  • how to set new range variable to one cell address
  • Include new data entered each day in concatenate formula
  • SSRS export to Excel UNFORMATTED
  • Is it possibule to insert 200 JPG image together in the excel file with the same size into its cell?
  • Lotus Notes Database Search
  • Extracting text from number of webpages list in Excel
  • how to remove special characters from all the cells in sheet
  • How can I clear the contents of (only) hidden cells in Excel (and not delete them)?
  • Using Application.OnKey to Disable CTRL-V
  • Not able to find unique values in excel
  • Excel stealing keyboard focus from VCL Form (in AddIn)
  • Multiplying an amount by 0.45 up until 833 then the remain by 0.25
  • Sort multiple rows Left-to-Right
  • Sorting data in columns with variable number of columns
  • Speeding up macro that goes through a workbook overwriting sheets by value
  • How can I optimize this macro that sorts and formats SQL data?
  • Copy a cell to a merged cell while keeping hyperlink value
  • Multiple IF arguments layered between cells
  • 432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded?
  • How to convert unusual datetime formats in excel to year, month, day, and time cells respectively?
  • Flashing animation getting interrupted by DoEvents
  • Delete multiple Excel Sheets in VBA
  • Delete text (left or right) with VBA so I only keep a specific string
  • I Bad: VBA Confusion
  • Add spaces to a string in excel vba, later used in .HTMLBody
  • Using While loop for getting the handle of rows in a particular Column
  • How to perform SUM of multiple VLOOKUP values by treating N/A values as Zero?
  • FIND function in VBA - Object does not support this property or Method
  • VBA Excel: “Automation Error. Exception occurred.” when using UserForm
  • Find specific lower or uppercase
  • group the results of a matching function
  • "Standard Deviation If" in VBA
  • How to use Conditional Formatting?
  • Match Function Shows Incorrect/Repeat Position
  • Add character to the beginning of a cell that start with number
  • How can I handle DLL errors in VBA?
  • Copy a Hidden Excel Sheet
  • How to import a table from web page (with "div class") to excel?
  • format excel file after export-csv powershell
  • Cancel = True is not working in Workbook_BeforeClose
  • Is there a way to retrieve information from a text file in vba?
  • Comparing two columns and if it matches then it should be highlighted in another column
  • How to get temperature of datetime value based on the table which contains day and night wise temperature data
  • How to Define a Pivot Cache?
  • Named argument not found vba compile error
  • Calling multiple Sub's with one line
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk