Good afternoon,
I managed although without having any VBA knowledge to design an userform to update/view a database.
what I want to do is:
- if "month" change, I want to see on the userform all the info of the 1 st record for the month selected
- when I click on next button, I want to go to the next record as follows:
A) if I am doing a search per month, then go to the next record with match the selected month (knowing that several references have the same month in common, however each record has a different "ref" (2015ct001,2015ct002 etc)
B)or If am looking per REF, go to the next REF (from 2015CT001 to 2015CT002 then to 2015CT003)
Any other suggestion to simplify the code is welcome.
Hope the above is clear enough and thank you in advance for your help
[CODE][/Private Sub PutData()
Dim IROW As Long
Dim IROW1 As Long
Dim WSH As Worksheet
Dim WSH1 As Worksheet
Set WSH = Worksheets("RECAP")
Set WSH1 = Worksheets("PRICING FINAL")
IROW = WSH.Cells.Find(WHAT:=Me.REF.Value, SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
IROW1 = WSH1.Cells.Find(WHAT:=Me.REF.Value, SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
WSH.Cells(IROW, "A").Value = Me.MONTH.Value
WSH.Cells(IROW, "D").Value = Me.OIC.Value
WSH.Cells(IROW, "E").Value = Me.VSL.Value
WSH.Cells(IROW, "F").Value = Me.GRADE.Value
WSH.Cells(IROW, "G").Value = Me.LP.Value
WSH.Cells(IROW, "I").Value = Me.CT_QTY.Value
WSH.Cells(IROW, "J").Value = Me.TOLERANCE.Value
WSH.Cells(IROW, "L").Value = Me.SUPPLIER.Value
WSH.Cells(IROW, "M").Value = Me.TERM_P.Value
WSH.Cells(IROW, "N").Value = Me.CT_DATES_TERM.Value
WSH.Cells(IROW, "O").Value = Me.CT_DATES.Value
WSH.Cells(IROW, "R").Value = Me.LP_INSP.Value
WSH.Cells(IROW, "S").Value = Me.LP_INSP_SHARING.Value
WSH.Cells(IROW, "U").Value = Me.LP_AGT.Value
WSH.Cells(IROW, "V").Value = Me.LP_AGT_CATEGORY.Value
WSH.Cells(IROW, "W").Value = Me.RCVRS.Value
WSH.Cells(IROW, "X").Value = Me.TERMS_S.Value
WSH.Cells(IROW, "Y").Value = Me.DP.Value
WSH.Cells(IROW, "Z").Value = Me.REQ_MIN_QTY.Value
WSH.Cells(IROW, "AA").Value = Me.REQ_MAX_QTY.Value
WSH.Cells(IROW, "AD").Value = Me.DP_INSP.Value
WSH.Cells(IROW, "AE").Value = Me.DP_INSP_SHARING.Value
WSH.Cells(IROW, "AG").Value = Me.DP_AGT.Value
WSH.Cells(IROW, "AH").Value = Me.DP_AGT_CATEGORY.Value
WSH.Cells(IROW, "AI").Value = Me.BL_DATE.Value
WSH.Cells(IROW, "AJ").Value = Me.COD_DATE.Value
WSH.Cells(IROW, "AK").Value = Me.BL_GROSS_QTY.Value
WSH.Cells(IROW, "AL").Value = Me.BL_NET_QTY.Value
WSH.Cells(IROW, "AM").Value = Me.OT_QTY.Value
WSH1.Cells(IROW1, "M").Value = Me.INV_QTY_P.Value
WSH1.Cells(IROW1, "O").Value = Me.PRICING_P.Value
WSH1.Cells(IROW1, "P").Value = Me.PMT_P.Value
WSH1.Cells(IROW1, "R").Value = Me.OSP_P.Value
WSH1.Cells(IROW1, "S").Value = Me.PREM_P.Value
WSH1.Cells(IROW1, "AE").Value = Me.INV_QTY_S.Value
WSH1.Cells(IROW1, "AG").Value = Me.PRICING_S.Value
WSH1.Cells(IROW1, "AH").Value = Me.PMT_S.Value
WSH1.Cells(IROW1, "AJ").Value = Me.OSP_S.Value
WSH1.Cells(IROW1, "AK").Value = Me.PREM_S.Value
WSH1.Cells(IROW1, "BB").Value = Me.MIN_FRT_QTY.Value
WSH1.Cells(IROW1, "BC").Value = Me.WS.Value
End Sub
Private Sub CMDADD_Click()
PutData
End Sub
Private Sub GetData()
Dim IROW As Long
Dim IROW1 As Long
Dim IROW2 As Long
Dim WSH As Worksheet
Dim WSH1 As Worksheet
Dim WSH2 As Worksheet
Dim ROW_NR As String
Set WSH = Worksheets("RECAP")
Set WSH1 = Worksheets("PRICING FINAL")
Set WSH2 = Worksheets("PANDL")
IROW = WSH.Cells.Find(WHAT:=Me.REF.Value, LookIn:=xlValues, SearchOrder:=xlRows, SearchDirection:=xlPrevious).Row
IROW1 = WSH1.Cells.Find(WHAT:=Me.REF.Value, LookIn:=xlValues, SearchOrder:=xlRows, SearchDirection:=xlPrevious).Row
IROW2 = WSH2.Cells.Find(WHAT:=Me.REF.Value, LookIn:=xlValues, SearchOrder:=xlRows, SearchDirection:=xlPrevious).Row
'COPY THE DATA TO USERFORM
With ThisWorkbook.Worksheets("RECAP")
Me.MONTH.Value = WSH.Cells(IROW, "A").Value
Me.PANDL_MONTH.Value = WSH.Cells(IROW, "A").Value
Me.REF.Value = WSH.Cells(IROW, "C").Value
Me.PANDL_REF.Value = Me.REF.Value
Me.OIC.Value = WSH.Cells(IROW, "D").Value
Me.PANDL_OIC.Value = WSH.Cells(IROW, "D").Value
Me.VSL.Value = WSH.Cells(IROW, "E").Value
Me.PANDL_VSL.Value = WSH.Cells(IROW, "E").Value
Me.GRADE.Value = WSH.Cells(IROW, "F").Value
Me.PANDL_GRADE.Value = WSH.Cells(IROW, "F").Value
Me.LP.Value = WSH.Cells(IROW, "G").Value
Me.PANDL_LP.Value = WSH.Cells(IROW, "G").Value
Me.CT_QTY.Value = WSH.Cells(IROW, "I").Value
Me.TOLERANCE.Value = WSH.Cells(IROW, "J").Value
Me.SUPPLIER.Value = WSH.Cells(IROW, "L").Value
Me.PANDL_SUPPLIER.Value = WSH.Cells(IROW, "L").Value
Me.TERM_P.Value = WSH.Cells(IROW, "M").Value
Me.PANDL_TERM_P.Value = WSH.Cells(IROW, "M").Value
Me.CT_DATES_TERM.Value = WSH.Cells(IROW, "N").Value
Me.CT_DATES.Value = WSH.Cells(IROW, "O").Value
Me.LP_INSP.Value = WSH.Cells(IROW, "R").Value
Me.LP_INSP_SHARING.Value = WSH.Cells(IROW, "S").Value
Me.LP_AGT.Value = WSH.Cells(IROW, "U").Value
Me.LP_AGT_CATEGORY.Value = WSH.Cells(IROW, "V").Value
Me.RCVRS.Value = WSH.Cells(IROW, "W").Value
Me.PANDL_RCVRS.Value = WSH.Cells(IROW, "W").Value
Me.TERMS_S.Value = WSH.Cells(IROW, "X").Value
Me.PANDL_TERMS_S.Value = WSH.Cells(IROW, "X").Value
Me.DP.Value = WSH.Cells(IROW, "Y").Value
Me.PANDL_DP.Value = WSH.Cells(IROW, "Y").Value
Me.REQ_MIN_QTY.Value = WSH.Cells(IROW, "Z").Value
Me.REQ_MAX_QTY.Value = WSH.Cells(IROW, "AA").Value
Me.DP_INSP.Value = WSH.Cells(IROW, "AD").Value
Me.DP_INSP_SHARING.Value = WSH.Cells(IROW, "AE").Value
Me.DP_AGT.Value = WSH.Cells(IROW, "AG").Value
Me.DP_AGT_CATEGORY.Value = WSH.Cells(IROW, "AH").Value
Me.BL_DATE.Value = WSH.Cells(IROW, "AI").Value
Me.PANDL_BL_DATE.Value = WSH.Cells(IROW, "AI").Value
Me.COD_DATE.Value = WSH.Cells(IROW, "AJ").Value
Me.BL_GROSS_QTY.Value = WSH.Cells(IROW, "AK").Value
Me.PANDL_BL_GROSS_QTY.Value = WSH.Cells(IROW, "AK").Value
Me.BL_NET_QTY.Value = WSH.Cells(IROW, "AL").Value
Me.PANDL_BL_NET_QTY.Value = WSH.Cells(IROW, "AL").Value
Me.OT_QTY.Value = WSH.Cells(IROW, "AM").Value
Me.PANDL_OT_QTY.Value = WSH.Cells(IROW, "AM").Value
End With
With ThisWorkbook.Worksheets("PRICING FINAL")
Me.INV_QTY_P.Value = WSH1.Cells(IROW1, "M").Value
Me.PANDL_INV_QTY_P_TERMS.Value = WSH1.Cells(IROW1, "M").Value
Me.PANDL_INV_QTY_P.Value = WSH1.Cells(IROW1, "N").Value
Me.PRICING_P.Value = WSH1.Cells(IROW1, "O").Value
Me.PANDL_PRICING_P.Value = WSH1.Cells(IROW1, "O").Value
Me.PRICING_S.Value = WSH1.Cells(IROW1, "AG").Value
Me.PANDL_PRICING_S.Value = WSH1.Cells(IROW1, "AG").Value
Me.PMT_P.Value = WSH1.Cells(IROW1, "P").Value
Me.OSP_P.Value = WSH1.Cells(IROW1, "R").Value
Me.PREM_P.Value = WSH1.Cells(IROW1, "S").Value
Me.PANDL_PREM_P.Value = WSH1.Cells(IROW1, "S").Value
Me.INV_QTY_S.Value = WSH1.Cells(IROW1, "AE").Value
Me.PANDL_INV_QTY_S_TERMS.Value = WSH1.Cells(IROW1, "AE").Value
Me.PANDL_INV_QTY_S.Value = WSH1.Cells(IROW1, "AF").Value
Me.INV_QTY_S.Value = WSH1.Cells(IROW1, "AE").Value
Me.PMT_S.Value = WSH1.Cells(IROW1, "AH").Value
Me.OSP_S.Value = WSH1.Cells(IROW1, "AJ").Value
Me.PREM_S.Value = WSH1.Cells(IROW1, "AK").Value
Me.PANDL_PREM_S.Value = WSH1.Cells(IROW1, "AK").Value
Me.MIN_FRT_QTY.Value = WSH1.Cells(IROW1, "BB").Value
Me.WS.Value = WSH1.Cells(IROW1, "BC").Value
Me.PANDL_PROV_P.Value = WSH1.Cells(IROW1, "X").Value
Me.PANDL_FINAL_P.Value = WSH1.Cells(IROW1, "W").Value
Me.PANDL_BAL_P.Value = WSH1.Cells(IROW1, "Y").Value
Me.PANDL_PROV_S.Value = WSH1.Cells(IROW1, "AP").Value
Me.PANDL_FINAL_S.Value = WSH1.Cells(IROW1, "AO").Value
Me.PANDL_BAL_S.Value = WSH1.Cells(IROW1, "AQ").Value
Me.PANDL_HEDGE.Value = WSH1.Cells(IROW1, "AY").Value
Me.PANDL_GROSS_FRT.Value = WSH1.Cells(IROW1, "BE").Value
Me.PANDL_SECA.Value = WSH1.Cells(IROW1, "BK").Value
Me.PANDL_TOT_SHIP.Value = WSH1.Cells(IROW1, "BQ").Value
End With
With ThisWorkbook.Worksheets("PANDL")
Me.PANDL_DEM_IN.Value = WSH2.Cells(IROW2, "AC").Value
Me.PANDL_DEM_OUT.Value = WSH2.Cells(IROW2, "T").Value
Me.PANDL.Value = WSH2.Cells(IROW2, "AI").Value
End With
Me.ROW_NR = Right(Me.REF.Value, 3)
Me.BL_DATE.Value = Format(BL_DATE.Value, "dd-mmm-yy")
Me.PANDL_BL_DATE.Value = Format(BL_DATE.Value, "dd-mmm-yy")
Me.COD_DATE.Value = Format(COD_DATE.Value, "dd-mmm-yy")
Me.PREM_P = Format(PREM_P.Value, "#,##0.00")
Me.PANDL_PREM_P = Format(PREM_P.Value, "#,##0.00")
Me.OSP_P = Format(OSP_P.Value, "#,##0.00")
Me.PREM_S = Format(PREM_S.Value, "#,##0.00")
Me.OSP_S = Format(OSP_S.Value, "#,##0.00")
Me.CT_QTY = Format(CT_QTY.Value, "#,##0.00")
Me.MIN_FRT_QTY = Format(MIN_FRT_QTY, "#,##0.00")
Me.BL_GROSS_QTY = Format(BL_GROSS_QTY.Value, "#,##0.00")
Me.PANDL_BL_GROSS_QTY = Format(BL_GROSS_QTY.Value, "#,##0.00")
Me.BL_NET_QTY = Format(BL_NET_QTY.Value, "#,##0.00")
Me.PANDL_BL_NET_QTY = Format(BL_NET_QTY.Value, "#,##0.00")
Me.OT_QTY = Format(OT_QTY.Value, "#,##0.00")
Me.PANDL_INV_QTY_P = Format(PANDL_INV_QTY_P, "#,##0.00")
Me.PANDL_INV_QTY_S = Format(PANDL_INV_QTY_S, "#,##0.00")
Me.PANDL_OT_QTY = Format(OT_QTY.Value, "#,##0.00")
Me.PANDL_PROV_P = Format(PANDL_PROV_P.Value, "#,##0.00")
Me.PANDL_PROV_S = Format(PANDL_PROV_S.Value, "#,##0.00")
Me.PANDL_FINAL_P = Format(PANDL_FINAL_P.Value, "#,##0.00")
Me.PANDL_FINAL_S = Format(PANDL_FINAL_S.Value, "#,##0.00")
Me.PANDL_BAL_P = Format(PANDL_BAL_P.Value, "#,##0.00")
Me.PANDL_BAL_S = Format(PANDL_BAL_S.Value, "#,##0.00")
Me.PANDL_GROSS_FRT = Format(PANDL_GROSS_FRT.Value, "#,##0.00")
Me.PANDL_SECA = Format(PANDL_SECA.Value, "#,##0.00")
Me.PANDL_TOT_SHIP = Format(PANDL_TOT_SHIP.Value, "#,##0.00")
Me.PANDL_DEM_IN = Format(PANDL_DEM_IN.Value, "#,##0.00")
Me.PANDL_DEM_OUT = Format(PANDL_DEM_OUT.Value, "#,##0.00")
Me.PANDL = Format(PANDL.Value, "#,##0.00")
Me.PANDL_HEDGE = Format(PANDL_HEDGE, "#,##0.00")
Me.TOLERANCE = Format(TOLERANCE.Value, "0%")
Me.LP_INSP_SHARING = Format(LP_INSP_SHARING.Value, "0%")
Me.DP_INSP_SHARING = Format(DP_INSP_SHARING.Value, "0%")
End Sub
Private Sub REF_Change()
GetData
End Sub
Private Sub UserForm_Initialize()
Me.REF.Value = "2015CT001"
GetData
End Sub
CODE]
Bookmarks