Posts by rakotonirinas

  • Lookup value copy and paste using VBA

    Hello specialists,


    I've got to attached files here. They are exactly the same in terms of contents and purposes except the piece of coding which have the same purpose or end result but look into different target to execute the code.


    Workbook (D13) calls for the year cell D13 to be changed in order for the code to function and populate values in Dand it does seem to function properly.


    Workbook (D7) calls for the cell D7 to be TRUE before the cells D17 to D37 get populated. The issue is that, the code links to this workbook doesn't see to work, yet it is a valid code I believe.


    Asking for help to see, what I may doing incorrectly. Codes were written with help from external sources.


    Thank you very much.

  • Edit Data using Userform based on 2 criteria

    Hello Carim,


    I'll try to answer the best I can.


    Quote

    1. Why have you modified your Originalfrm to relfect your question ... from a design standpoint : where are your TWO search fields ...?


    because I wanted to show how it is done for ONE criterion and how it should look like for 2 criteria based on both the supplier and the reporting month. It is two search fields because these are the two criteria, which could be combined through concatenate for instance in order to make it easier. Nonetheless, we look into both criteria supplier and reporting month and only populate the rest of the fields on the form if this search criteria exist at the same time.


    Quote

    2. A Userform is nothing but ... a so-called simplification for the User ... (often its is not ... it is worse ...)


    I'm hoping, this will be more of a simplification rather than the opposite as the real data is more than 4 row longs (8500 so far) and there are 18 columns to look at as well.


    Filter is a good idea but I believe there is a quick way of doing it, which I'm still trying to figuring it out and asking fro help to that direction as well.

  • Edit Data using Userform based on 2 criteria

    Hello,


    I would like to get some help please in my attached document.


    The purpose of the userform is to retrieve current values in each non-blank cell of a row based on 2 criteria,i.e., Reporting Month and Supplier and to edit them and rewrite (update) the Excel data accordingly.


    The issue: I was able to do for ONE criterion, which is the Invoice. However, the invoice will only be raised later based on the above criteria (reporting month and supplier). So as you can see form the Originalfrm, which is the ONE criterion. I could search, edit and save.


    The question: How can I use the same process to include TWO criteria as per the Userform1? The Reporting Month will have the format mmm-yy and only if these TWO criteria are met that the other textboxs of the userform be populated. For the Reporting Month textbox. I can probably use a drop down list, which will then be a Combobox or create 2 other Comboboxes of Year and Month on a drop down list and create a code that will concatenate the two values.


    I found similar post https://www.ozgrid.com/forum/f…reating-a-duplicate-entry but like my Originalfrm, it is only based on ONE criterion.


    Thank you for your time and I appreciate your help.


    Best.

  • Prevent Duplicate Record in Userform Data Entry

    Dear [USER="138669"]Mumps[/USER]. Just nearly there. I know it's not too far.


    The issue is that the 'Already Reported!' message box comes up every time although there is no duplication. I'm unsure where did I get it incorrect.
    Thank you.


    [VBA]
    Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False

    'Checking duplicate


    Dim ws As Worksheet
    Dim LRow As Long
    Dim ctl As Object
    Dim supplier As Object
    Dim RngList As Object


    Set ws = ThisWorkbook.Sheets("Sales")

    LRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    With ws
    .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0) = Me.ComboBox1.Text
    .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0) = Me.ComboBox3.Text
    .Cells(.Rows.Count, "H").End(xlUp).Offset(1, 0) = Me.ComboBox4.Text
    .Cells(.Rows.Count, "I").End(xlUp).Offset(1, 0) = Me.ComboBox5.Text
    .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) = Me.ComboBox6.Text
    .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0) = Me.ComboBox2.Text
    .Cells(.Rows.Count, "L").End(xlUp).Offset(1, 0) = Me.TextBox4.Text
    .Cells(.Rows.Count, "M").End(xlUp).Offset(1, 0) = Me.ComboBox8.Text
    .Cells(.Rows.Count, "O").End(xlUp).Offset(1, 0) = Me.ComboBox9.Text
    .Cells(.Rows.Count, "R").End(xlUp).Offset(1, 0) = Me.TextBox2.Text
    .Cells(.Rows.Count, "T").End(xlUp).Offset(1, 0) = Me.TextBox3.Text
    End With

    LRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Set RngList = CreateObject("Scripting.Dictionary")
    For Each supplier In ws.Range("G2:G" & LRow)
    If Not RngList.Exists(supplier.Value & "|" & Year(supplier.Offset(0, 8)) & "|" & Month(supplier.Offset(0, 8))) Then
    RngList.Add supplier.Value & "|" & Year(supplier.Offset(0, 8)) & "|" & Month(supplier.Offset(0, 8)), Nothing
    Else
    MsgBox ("Already Reported!")
    Intersect(ws.Range("F:M,O:O,R:R,T:T"), ws.Rows(LRow)).ClearContents

    For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Then
    ctl.Value = ""
    End If
    Next ctl
    Application.ScreenUpdating = True
    Exit Sub
    End If
    Next supplier
    RngList.RemoveAll

    For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Then
    ctl.Value = ""
    End If
    Next ctl
    Application.ScreenUpdating = True
    End Sub
    [/VBA]

  • Prevent Duplicate Record in Userform Data Entry

    The userform has only 9 boxes of combination of text, combo and list boxes, but the Sales sheet has 25 boxes. And the value within the 9 boxes of the userform when recording will not be aligned on a column one after the other but would be scattered throughout the Sales sheet, something like:
    box 1 (ComboBox) = Column A
    box 2 (ComboBox) = Column D
    box 3 (TextBox) = Column E
    box 4 (TextBox) = Column I
    box 5 (ComboBox) = Column J
    box 6 (ComboBox) = Column M
    box 7 (TextBox or Date Picker) = Column Q
    box 8 (ComboBox) = Column R
    box 9 (ComboBox) = Column T


    In effect, the other columns, namely B, C, F, G, H, K, L, N, O, P, S, U, V, W, X, Y are either populated through non vba formula (function) as result of the value taking the value from one or more columns from the userform box that has (have) been pasted on the Sales sheet now or just as one value column, which doesn't change irrespective of the other columns value.


    So in my slim version of the sheet, attached above, column E and F will be then the one not showing on the userform but it depends on the value of column G which corresponds initially to the TextBox6 in the userform.


    I'm not sure whether my explanation is clearer.


    Thank you

  • Prevent Duplicate Record in Userform Data Entry

    Somehow, access to the forum and OzGrid is rather painful any time prior 2 PM UK time.


    You're absolutely correct [USER="138669"]Mumps[/USER], it should be the column G and not B, that was a mistake.


    For my first question. Since the userform has only 5 boxes and the Sales sheet 7, how can the code be amended to accommodate the extra columns when pasting the values on the userform to the Sales sheet - that is, I assume to tweak the following code:
    [VBA]
    desWS.Range("B" & LastRow + 1).Resize(1, 5) = Array(Me.TextBox7.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox6, Me.TextBox5.Value) [/VBA]


    The second question is really the same as the first. How can I tweak your code to accommodate 25 columns, in which the corresponding columns of the userform values are not aligned one after the other but scattered all over the 25 columns?


    Thank you.

  • Prevent Duplicate Record in Userform Data Entry

    Hi [USER="138669"]Mumps[/USER]. This is much appreciated. I have requested the change to be made as I'm using a work laptop.


    Regarding the VBA itself, I did perfectly work. However, I would like more help please on the following (I have slightly amended both the Sales sheet and the VBA, although I rather kept the initial userform):
    - If columns E and F are populated through formula drawn from the corresponding value from column B which in effect comes from the userform TextBox6, how can I write the code so that it takes into consideration these 2 columns when pasting (writing) the userform value to the Sales sheet?
    - As this is just a simple example of a bigger work, my original work has 25 columns in total with only 9 boxes that need to be completed from the userform and the rest is just formula on the worksheet. How can I write (record) the values from the userform (taking the attached example) to the Sales worksheet and have them in the correct place?
    In other words, I guess I got a bit confused here [VBA]desWS.Range("B" & LastRow + 1).Resize(1, 5) = Array(Me.TextBox7.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox6, Me.TextBox5.Value)[/VBA]


    Your help and help from others are much appreciated.


    Thank you.

  • Prevent Duplicate Record in Userform Data Entry

    Dear all,


    I have a Sales worksheet that will be populated through the Userform (as below) which consists of 5 columns. The userform Record button will write the contents of the form to the Sales worksheet.


    I know how to write the code to write the value in every textbox, combobox or else to the worksheet.


    However, I would like to prevent duplication by checking first if value in 2 boxes in the userform are not already on the Sales worksheet. So therefore, if there is a duplication of those 2 values simultaneously, then the Sub will display a message box "Duplicate!" and then will Exit Sub.


    On the Sales spreadsheet, the 2 columns that will be checked for duplication are D (Supplier) and E(Reporting Month).


    So as an example based on the data already on the worksheet, if the userform includes the Silo Tours and Jun-18 to be recorded, this will display the pop up warning message of Duplicate and will exit the sub. However, if it is Silo Tours and Aug-18 then it will write the values on the form into the corresponding columns cells.


    I think there are various ways of doing such as through filter, Countif or Index Match and etc.


    So I need a VBA code to check whether the combination Supplier and Reporting MOnth do not already exist on the Sales worksheet.


    Can you help please. Thank you for your time and consideration.


    [ATTACH=JSON]{"alt":"VBA Form","data-align":"none","data-size":"full","title":"Userform to Record","data-attachmentid":1212887}[/ATTACH]

  • Using Index Match in Loop on VBA

    Hello experts,
    Can you please help on the following?


    I think what I am attempting to achieve is quite simple but somehow I could not manage to make it work on VBA.


    As per my attached spreadsheet, I have 2 worksheets. The 'OCT18' is the master one and SBM_18 extract data from the master 'OCT18' one.
    Without VBA, I will simply use a Vlookup or Index Match such as the below


    =INDEX('OCT18'!B$2:B$50,MATCH(SBM_18!$A2,'OCT18'!$A$2:$A$50,0),1) > for the No. in 'SBM_18'
    =INDEX('OCT18'!C$2:C$50,MATCH(SBM_18!$A2,'OCT18'!$A$2:$A$50,0),1) > for the Department No. in 'SBM_18'
    =INDEX('OCT18'!D$2:D$50,MATCH(SBM_18!$A2,'OCT18'!$A$2:$A$50,0),1) > and for the Member No. in 'SBM_18'


    Now I could copy and paste the value from 'OCT18' (B:D) to 'SBM'(B:D) but the values in the master worksheet will get updated every week so needs some form of formulas or function to work it out.


    Also, as I have more formulas applied elsewhere and a bigger data than what is presented here, I think using a VBA Code will help for this - I hope basic - lookup function. Yet, with the following code, I'm kind of stuck and unsure how to resolve the issue.


    I appreciate your help.