Populate listbox from a range and filter listbox data with two comboboxes and show the filtered data in the listbox,vba teacher sourav,kolkata 09748184075

Private Sub ComboBox1_Change()
Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "mm/dd/yyyy")

End Sub

Private Sub ComboBox2_Change()
Me.ComboBox2.Value = Format(Me.ComboBox2.Value, "mm/dd/yyyy")
End Sub

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim firstdate As Date
Dim timekey1 As Integer
Dim timekey2 As Integer

firstdate = CDate(UserForm1.ComboBox1.Text)
Dim seconddate As Date
seconddate = CDate(UserForm1.ComboBox2.Text)

Sheets("Dates").Select
Range("B2").Select
Do
If firstdate = CDate(ActiveCell.Value) Then

'MsgBox (Replace(ActiveCell.address, "$", ""))

timekey1 = CInt(ActiveCell.Offset(0, -1).Value)
'MsgBox (timekey1)
Exit Do

Else
ActiveCell.Offset(1, 0).Select

End If
Loop While ActiveCell.Value <> ""

Sheets("Dates").Select
Range("B2").Select
Do
If seconddate = CDate(ActiveCell.Value) Then
timekey2 = CInt(ActiveCell.Offset(0, -1).Value)
'MsgBox (timekey2)
Exit Do

Else
ActiveCell.Offset(1, 0).Select

End If
Loop While ActiveCell.Value <> ""
Dim address As String
address = "A" & CStr(timekey1 + 1)
Sheets("Dates").Select
Range(address).Select

Dim address2 As String
address2 = "A" & CStr(timekey2 + 1)
Range(address2).Select
'Range(address & ":" & address2).Select
Dim timekeyarr() As Integer
ReDim timekeyarr(timekey2) As Integer
Dim count As Integer
count = 1
Dim tempaddr As String
tempaddr = address
Range(tempaddr).Select
While Replace(ActiveCell.address, "$", "") <> address2
timekeyarr(count - 1) = CInt(ActiveCell.Value)
count = count + 1

ActiveCell.Offset(1, 0).Select

Wend
'MsgBox (count)
timekeyarr(count - 1) = CInt(ActiveCell.Value)


'For i = 0 To UBound(timekeyarr) - 1
'MsgBox (timekeyarr(i))
'Next i
Dim currencykeyarr() As Integer
ReDim currencykeyarr(UBound(timekeyarr)) As Integer
'MsgBox (UBound(currencykeyarr))

Sheets("Data").Select
Range("B2").Select

Dim temppos As String
temppos = Replace(ActiveCell.address, "$", "")

For i = 0 To UBound(timekeyarr) - 1
Range(temppos).Select
Do
If CInt(ActiveCell.Value) = timekeyarr(i) Then
currencykeyarr(i) = CInt(ActiveCell.Offset(0, -1).Value)
Exit Do
Else
ActiveCell.Offset(1, 0).Select

End If


Loop While CStr(ActiveCell.Value) <> ""
Next i

'For i = 0 To UBound(currencykeyarr) - 1

'MsgBox (currencykeyarr(i))
'Next i
'let's go to the currency sheet and creat the filtered data
On Error Resume Next
Sheets("tempdata").Delete

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(after:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
    ws.Name = "tempdata"
    Sheets("tempdata").Select
'action start
Dim temppos2 As String
Sheets("Currencies").Select
Range("A2").Select


temppos = Replace(ActiveCell.address, "$", "")

For i = 0 To UBound(currencykeyarr) - 1
Range(temppos).Select
Do
If CInt(ActiveCell.Value) = currencykeyarr(i) Then
temppos2 = Replace(ActiveCell.address, "$", "")
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("tempdata").Select
With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Currencies").Select
Range(temppos2).Select
ActiveCell.Offset(1, 0).Select

Exit Do
Else
ActiveCell.Offset(1, 0).Select

End If


Loop While CStr(ActiveCell.Value) <> ""
Next i


Application.CutCopyMode = False

Sheets("tempdata").Select
Range("A1").Select
If ActiveCell.Value <> "" Then

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    End If
    'MsgBox (Selection.address)
    
    Me.ListBox1.Clear
    Me.ListBox1.RowSource = Selection.address
    
    
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Financial calculation using combobox and label in vba,VBA Teacher Sourav,Kolkata 09748184075

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
Dim pos As Integer
Dim result1 As Double
Dim firstval1 As Double
Dim combodata As String
Dim count1 As Integer
Dim covar1 As Double

count1 = 0

If CStr(Me.ComboBox1.Text) <> CStr(Me.ComboBox2.Text) Then
combodata = Me.ComboBox1.Text
'MsgBox (combodata)

Sheets("Data").Select

'Sheets("Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=MATCH(""" & combodata & """,listrange,0)"
pos = ActiveCell.Value
ActiveCell.Value = ""
Sheets("Data").Select
Range("A1").Select

For i = 1 To pos
ActiveCell.Offset(1, 0).Select
Next i
'MsgBox (ActiveCell.Value)
ActiveCell.Offset(0, 2).Select
firstval1 = CDbl(ActiveCell.Value)

While ActiveCell.Value <> ""

result1 = result1 + ActiveCell.Value
count1 = count1 + 1

ActiveCell.Offset(0, 1).Select
Wend
covar1 = (result1 - firstval1) / count1
MsgBox ("count1 is " & count1)
MsgBox ("firstval1 is " & firstval1)
MsgBox ("sum is " & result1)
MsgBox (covar1)
'this is for combobox2


Dim pos2 As Integer
Dim result2 As Double
Dim firstval2 As Double
Dim combodata2 As String
Dim count2 As Integer
Dim covar2 As Double

count2 = 0


combodata2 = Me.ComboBox2.Text
'MsgBox (combodata)

Sheets("Data").Select

'Sheets("Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=MATCH(""" & combodata2 & """,listrange,0)"
pos2 = ActiveCell.Value
ActiveCell.Value = ""
Sheets("Data").Select
Range("A1").Select

For i = 1 To pos2
ActiveCell.Offset(1, 0).Select
Next i
'MsgBox (ActiveCell.Value)
ActiveCell.Offset(0, 2).Select
firstval2 = CDbl(ActiveCell.Value)

While ActiveCell.Value <> ""

result2 = result2 + ActiveCell.Value
count2 = count2 + 1

ActiveCell.Offset(0, 1).Select
Wend
covar2 = (result2 - firstval2) / count2
MsgBox ("count2 is " & count2)
MsgBox ("firstval2 is " & firstval2)
MsgBox ("sum is " & result2)
MsgBox (covar2)
Me.Label1.Caption = CStr(covar1 / covar2)
Else

End If


End Sub

Private Sub UserForm_Click()

   
    
    
    
    
End Sub

Private Sub UserForm_Initialize()
 Me.ComboBox1.List = Sheets("Data").Range("A2:A11").Value
    
 Me.ComboBox2.List = Sheets("Data").Range("A2:A11").Value
    

End Sub

copy data from listbox to listbox in vba,vba teacher Sourav,kolkata 09748184075

Private Sub CommandButton1_Click()
Dim signal As Boolean
signal = False
Dim i As Integer
i = ListBox2.ListCount - 1

 While i >= 0
 'MsgBox (i)
 
 
      
          If UserForm1.ListBox2.Selected(i) Then
          
              'MsgBox (ListBox1.List(i))
          
 For j = 0 To UserForm1.ListBox1.ListCount - 1
 If CStr(UserForm1.ListBox2.List(i)) = CStr(UserForm1.ListBox1.List(j)) Then
 'MsgBox ("wrong")
 signal = True
 Exit For
 End If
 Next j
 
 
          
If signal = True Then

Else
'MsgBox ("getting inside")
UserForm1.ListBox1.AddItem (UserForm1.ListBox2.List(i))
UserForm1.ListBox2.RemoveItem (i)

End If

End If
signal = False

          i = i - 1
           Wend
           
           


End Sub

Private Sub CommandButton2_Click()
Dim signal As Boolean
signal = False
Dim i As Integer
i = ListBox1.ListCount - 1

 While i >= 0
 'MsgBox (i)
 
 
      
          If UserForm1.ListBox1.Selected(i) Then
          
              'MsgBox (ListBox1.List(i))
          
 For j = 0 To UserForm1.ListBox2.ListCount - 1
 If CStr(UserForm1.ListBox1.List(i)) = CStr(UserForm1.ListBox2.List(j)) Then
 signal = True
 Exit For
 End If
 Next j
 
 
          
If signal = True Then

Else
UserForm1.ListBox2.AddItem (UserForm1.ListBox1.List(i))
UserForm1.ListBox1.RemoveItem (i)

End If

End If
signal = False

          i = i - 1
           Wend
           
           

End Sub

Private Sub ListBox2_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Sheets("Data").Select
Dim source As Range
Range("A2").Select
    While ActiveCell.Value <> ""
    UserForm1.ListBox1.AddItem (ActiveCell.Value)
    ActiveCell.Offset(1, 0).Select
    Wend
    
    
    
    
    
    
    
    
End Sub

Sub test()
For j = 0 To UserForm1.ListBox1.ListCount - 1
 UserForm1.ListBox1.RemoveItem j
 Next j
End Sub

French MBA College VBA Assignment done,vba teacher sourav,kolkata 09748184075

Sub advancedfilter()
Sheets("Extraction").Select
Cells.Clear
Range("A1").Select
ActiveCell.Value = "CustomerID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "DateandTime"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Date"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Time"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "ProductID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "ProductName"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Unit Price"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Quantity"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Total Price"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Discount"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Final Price"
ActiveCell.Offset(0, 1).Select

'January Sheet data copy


Sheets("January").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False

'February Month Data Copy

Sheets("February").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


'March Month Data Copy

Sheets("March").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False

Sheets("Extraction").Select
Range("C2").Select

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=TEXT(RC[-1],""d"")&""/""&TEXT(RC[-1],""m"")&""/""&TEXT(RC[-1],""yy"")"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=TIME(HOUR(RC[-2]),MINUTE(RC[-2]),SECOND((RC[-2])))"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
   ActiveCell.Range("A1:A12000").Select

Sheets("Extraction").Select
Range("F2").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Products!R3C1:R18C3,2,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Products!R3C1:R18C3,3,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select


Range("I2").Select

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    
    Range("J2").Select
      ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Extraction!RC[-1],Discounts,2,TRUE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    Selection.NumberFormat = "0.00%"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0%"
    
    
    Range("K2").Select
    
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-(RC[-2]*RC[-1])"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    Selection.NumberFormat = "0.00"

Range("A:Z").EntireColumn.AutoFit


End Sub

Filtering data from multiple sheets and consolidate into one,french mba college assignment,vba teacher sourav,kolkata 09748184075

Sub filterdata()

Sheets("Extraction").Select
Cells.Clear
Range("A1").Select
ActiveCell.Value = "userID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Start"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "End"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Duration"

ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Amount"

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
MsgBox (ws.Name)

If ws.Name = "D1" Or ws.Name = "D2" Or ws.Name = "D3" Then


Sheets(ws.Name).Select

Dim rList As Range
 On Error Resume Next
With Worksheets(ws.Name).ListObjects("Table1")
    Set rList = .Range
    .Unlist                           ' convert the table back to a range
End With


'filter D1
Sheets(ws.Name).Select

Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection.Address), , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight1"
    ActiveSheet.ListObjects("Table2").Name = "Table1"
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:= _
        "<8:0 br="" operator:="xlAnd">   
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Extraction").Select
    
    

With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

    ActiveSheet.Paste
    
    'filtering D1 completed
    'convert table to range
    
    
Sheets(ws.Name).Select


 On Error Resume Next
With Worksheets(ws.Name).ListObjects("Table1")
    Set rList = .Range
    .Unlist                           ' convert the table back to a range
End With

'table converted to range

   Application.CutCopyMode = False
   End If
   
Next ws
End Sub