Using the data file in excel called CARS. Excel sheet: https://drive.google.com/
ID: 3736593 • Letter: U
Question
Using the data file in excel called CARS.
Excel sheet: https://drive.google.com/file/d/1QdWlJuk4JL1FqTkyhcrZSeKsNR6RdSEs/view
Develop a form in VBA (VIsual Basic) that calculates and displays the results in a "List BOX" of the following questions:
1) How many cars of 8 CLY cylinders, weigh more than 3600 WGT pounds and have an MPG less than 18.
2) List of ENG model engines, which are 4 cylinders and weigh less than 2200 pounds WGT.
3) Which is the best ENG engine model that has the highest MPG, lowest CYL cylinder and least WGT Weight in this table.
(need the code of each question in VBA excel)
You can use "Option button" or create a button by question, it is at your discretion. There must be a button to clean the list.
2 234705205 2 33222221222 2 L G 2 2 5 0 2 2 5 G3333344443 0 441112 88888888888888466644444468888444 858675444554544 8176545610019785 2 F 12345678911234567 8901 345678901 222222233Explanation / Answer
ANS:-
Given that,
How many cars of 8 CLY cylinders, weigh more than 3600 WGT pounds and have an MPG less than 18.
1)
Sub Smallest_Value_Highlight_Address()
'Determines smallest value in range, highlights it and returns its address
'Cells with dates also return a value, and get covered for determining smallest value. Percentages will convert and return numerics.
'Determines values from the active worksheet
Dim strData As String
Dim rng As Range
Dim vValue As Variant
Dim rngCol As Range
Dim lngRow As Long
Dim rngAdd As Range
'Enter desired range in which to find the smallest value
strData = "A1:Z100"
Set rng = Range(strData)
'Determines smallest value in range
vValue = Application.WorksheetFunction.Min(rng)
For Each rngCol In rng.Columns
'Determines in case the smallest value exists in a particular column
If Application.WorksheetFunction.CountIf(rngCol, vValue) > 0 Then
'Returns row number of the smallest value, in the column which has the same
lngRow = Application.WorksheetFunction.Match(vValue, rngCol, 0)
'Returns cell address of the smallest value
Set rngAdd = rngCol.cells(lngRow, 1)
'Selects smallest value to highlight with color
rngAdd.Select
With Selection
.Interior.Color = RGB(255, 255, 0)
End With
'Message displays the searched range, smallest value, and its address
MsgBox "Smallest Value in Range(""" & strData & """) is " & vValue & ", in Cell " & rngAdd.Address & "."
Exit Sub
End If
Next
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.