Posts by RP_1985

    Friends, I need a formula which can extrapolate the values based on the % value changes. Here are below 2 examples with desired result what I need


    E.g. (1)
    I got difference of 10% (range between 50 to 60%) and difference in value is 50 (500 to 550). Hence for every % increase the value should also get increased by 5.
    So I need a formula where if the percentage increases by 1% then my output should become 505 and if % increased by 2% then my output should become 510.


    E.g. (2)
    I got difference of 15% (range between 75 to 90%) and difference in value is 300 (1200 to 1500). Hence for every % increase the value should also get increased by 45.
    So I need a formula where if the percentage increases by 1% then my output should become 1245 and if % increased by 2% then my output should become 1290 and so on.


    These are 2 scenario and the range will change basis requirement.


    Any help will be much appreciated

    Re: Populate Dates in different cells between date range - VBA


    How do I eliminate weekends from the summary that shows up in sheet2. E.g.: in attached sheet I got date range from 16-Nov to 23-Nov for all Type. When I run the macro in sheet2 I am getting 16-Nov, 17-Nov, 18-Nov, 19-Nov, 20-Nov, 21-Nov, 22-Nov, 23-Nov


    I do no need weekends and but should show only 16-Nov, 17-Nov, 18-Nov, 19-Nov, 20-Nov, 23-Nov


    I need to clear the cells based on the drop down selection of userform. I got Jan, Feb, Mar till Dec as drop down in my userform, if I select Jan I need to clear all cells which has Jan (i.e. 1-Jan, 2-Jan and so on).. With below code I am able to clear cells in given range that has "Jan" but not dates..



    Any help much appreciated



    User form

    Code
    Private Sub UserForm_Initialize()
    ComboBox1.List = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    End Sub



    Code
    For Each cell In Range("A1:CK3000")
     If cell.Value = ComboBox1.Text Then cell.Clear
     Next
     Unload Me
     ActiveSheet.Range("A1").Select

    Re: Select Column till last active cell based on current selection of row - VBA


    With the below code I am able to find the last row and last column but not able to paste the D3 value in all cells i.e. I need to copy D3 value and paste from D3 to L18. this is just example and range D3 to L18 will vary each time.




    Re: Match + Lookup - VBA


    Stephen, I just need the dates for batch# which are missing from LRA tab. But the above code is giving me all values row like Batch #, Batch Name and dates.


    What I need:
    [TABLE="width: 467"]

    [tr]


    [td]

    Batch #

    [/td]


    [td]

    Batch Name

    [/td]


    [td]

    Type

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    E232

    [/td]


    [td]

    R33

    [/td]


    [td]

    Sett

    [/td]


    [TD="align: right"]3-Apr-15[/TD]
    [TD="align: right"]10-Apr-15[/TD]
    [TD="align: right"]14-Apr-15[/TD]

    [/tr]


    [/TABLE]


    The above code is giving me:
    [TABLE="width: 601"]

    [tr]


    [td]

    Batch #

    [/td]


    [td]

    Batch Name

    [/td]


    [td]

    Type

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    E232

    [/td]


    [td]

    R33

    [/td]


    [td]

    Sett

    [/td]


    [td]

    E232

    [/td]


    [td]

    R33

    [/td]


    [TD="align: right"]3-Apr-15[/TD]
    [TD="align: right"]10-Apr-15[/TD]
    [TD="align: right"]14-Apr-15[/TD]

    [/tr]


    [/TABLE]

    Re: Match + Lookup - VBA


    Stephen <> Works perfect but one more last change please.. sorry I came across this scenario while working on larger set of data..


    If Batch #E232 is found in ADM tab but missing from LRA, I should be able to populate all the dates from ADM in Result tab for Batch #E232.

    I have set of cells in row column D which is already selected and now I need to select the columns for same range of column D till the last cell of row 2.


    Basically, if my selection in column D (D3:D15) and I got data in row 2 till K2, then my selection should be from D3 to K15. I got the below code but it is not giving me the result. The range can vary.


    Code
    Dim LastCol As Integer
        With ActiveSheet
            LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
            .Range("D3" & LastCol).Select
                   
             End With

    Re: Match + Lookup - VBA


    With the below code, I am just having one challenge. In the attached spread sheet, Batch #E232 of Result tab (row 2) is not available in either ADM or LRA, But when I run the below code the row2 in Result should be blank and move to the next lookup but I am getting result of Batch X1 in row 2.




    Re: Match + Lookup - VBA


    Thanks, It works but need few minor changes..
    1. Supplied data rows are not fixed, so I would need this lookup to loop till last active cell of column A
    2. Quoted data will be in Sheets2 starting range ("A1").. how can I change the reference.


    I tried changing on your code but no luck.

    I am trying to lookup values in Supplied summary and comparing with Quoted summary details. Dates in Supplied summary which are missing from Quoted summary will be my result for a batch.
    For E.g. Row 2 (Batch # A123) has got 04/03, 04/10 and 04/14, however in row 12 (Batch # A123) has 04/03 and 04/10 only. Hence my result in Cell J2 should be 04/14.
    This should continue for all batches.


    I am not an expert on VBA coding, but would like to learn more.. Any help will be appreciated.

    Re: VBA - first to match Name with data range and perform Hlookup


    Thanks jindon.
    By using the same concept, I am trying to lookup values in Supplied summary and comparing with Quoted summary details. Dates in Supplied summary which are missing from Quoted summary will be my result for a batch.


    For E.g. Row 2 (Batch # A123) has got 04/03, 04/10 and 04/14, however in row 12 (Batch # A123) has 04/03 and 04/10 only. Hence my result in Cell J2 should be 04/14.


    This should continue for all batches.



    I am not an expert on VBA coding, but would like to learn more.. Any help will be appreciated.

    I got the below data in a cell and trying to eliminate all 0's and update only dates in consecutive cells. Not sure if this can be using excel formula or VBA.


    I got this data in multiple rows. Any help will be much appreciated.


    Result: below data is in A1.. I would like to populate B1 with 03-Apr-2015, C1 with 08-Apr-2015, D1 with 27-Apr-2015 and E1 with 28-Apr-2015.. This format can differ each time..


    0,0,03-Apr-2015,0,0,0,0,08-Apr-2015,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,27-Apr- 2015,28-Apr- 2015,0,0