Posts by B Rama Krishna

    Hi,


    Great that it takes care of your requirement.


    You do not need to mention anything about A column or range. For every cell in E column the procedure checks if it is a blank, if so the .offset(0,-4) code checks if there is any value in col A in the same row. If there is a value, then it will not allow the saving of the workbook till the corresponding E cell is filled up. If there is no value in A then, next E cell is checked.


    It would work for all the rows without mentioning of the Col A or the row number explicitly. Pl choose the correct worksheet and run it.


    Pl let me know if there is a problem.

    Hi,


    Your requirement is "If A&row has a value, workbook cannot be saved unless E&row is filled". If so, pl try the following code

    Hi,


    Use 'xlpasteall' to retain the formats


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. ' Just double-click on any cell in Source Row (Columns A to M)
    3. Dim l As Long, x As Long
    4. x = Target.Row
    5. l = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    6. Sheet1.Range("A" & x & ":M" & x).Copy
    7. Sheet2.Range("A" & l & ":M" & l).PasteSpecial (xlPasteAll)
    8. Application.CutCopyMode = xlCut
    9. Cancel = True
    10. End Sub

    Hi,


    1. As you can see, there is no set pattern for the location of the dash based on the state. (eg. 'NJ' - sometimes there is no dash, sometimes it appears any where). Given this scenario, i do not think it is possible to factor that into the programme. Some senior member of this forum may have a better solution..


    2. For state 'AR', the dash appears in 4th position from right (at least in both the instances in your file). I have changed the code to insert the dash in the same position, if the state is chosen as 'AR' in Col F, L, R. You may enter the position against the state in sheet1 Col C. The state needs to be chosen before entering the vehicle number.


    The file is attached.

    Hi,


    1. The problem with dash appearing in the in the cells for the state abbreviations (assuming the abbreviation is contains only two letters) is sorted out. Also information entered (upto 2 letters) is converted into upper case.


    2 Is there a norm for the dash to appear depending on the state ? If so, pl indicate against each state in Col C of sheet 1 so that it can be incorporated


    3. Dash not appearing in cells happens if the application.EnableEvents is set to false (like exiting the sub routine midway - which can happen during debug mode). Also pl make sure that there are no blank spaces already before entering any data into a cell as the blank space is treated as a character.


    Pl check the attached file and give information on point 2 so that the same can be incorporated

    Hi ,


    Pl run the Splitcells subroutine. The unmerged data is stored in worksheet ("FinalData")



    It is preferable to have some data in each column.


    Pl let me know if this is what you are looking for.


    The file is attached.

    Hi,


    Hope the following meets your requirement. It introduces a "-" at a random position (2nd to 1 less than the length of the string) in the string.



    You can tweak the position of the dash if you have some other criteria for fixing its position.

    Hi,


    Pl let me know if my understanding is correct. For the cell values in Col A of sheet 1


    a) Retain rows with only 'Data', 'Date1' and 'Data2'
    b) From this data remove rows with 'Data' and 'Data2' (that leaves rows with only 'Data1')
    c) then perform the matching with Col A of Sheet 2 and delete non-matching rows


    In any case would appreciate if you could send an xl sheet and explain what needs to be done, so that I can work on the same.

    Hi Aroy,


    Pl try this code. The file is also attached. You can delete the sheet 'Data'


    Hi,


    Sorry, I could not fully understand your problem. But I have tried the following in the attached file.


    I used the Datedif function to get the months difference from two dates and named the range as 'intCells' and referred its value in the procedure 'AutofillRange2'.


    It is working fine whether I delete or insert the rows.


    Pl check this file and let me know if your problem is solved. If not, request you to send me a copy of the file explaining the problem faced.

    Hi,


    Pl assign the below code to a button and run from any cell


    Code modified

    Thanks Carim.


    I am new to this forum (or for that matter any forum as I have just some knowledge and enthusiasm ) and so not very clear about the code posting.


    Thanks for the advice on Select . Private procedure was more out of habit.


    Shall keep this in mind in future.

    Thanks Carim. I am new to this forum (or for that matter any forum as I have just some knowledge and enthusiasm ) and so not very clear about the code posting.


    Thanks for the advice on Select . Private procedure was more out of habit.


    Shall keep this in mind.

    Hi,


    Pl try this


    Private Sub RangeAutofill()


    '
    ' Select a cell with the info for autofill and run the sub to autofill
    ' in the next 3 cells before it. The procedure creates three columns to the right of active cell


    Dim lCol As Long
    Dim lrow As Long

    Dim rng As Range

    Dim i As Integer
    i = 0

    Set rng = ActiveCell

    lCol = rng.Column
    lrow = rng.Row


    rng.EntireColumn.Select

    selection.Insert Shift:=xlToRight
    i = i + 1
    selection.Insert Shift:=xlToRight
    i = i + 1
    selection.Insert Shift:=xlToRight
    i = i + 1

    rng.Select

    selection.AutoFill Destination:=Range(rng, rng.Offset(0, -i)), Type:=xlFillDefault

    End Sub


    The file is also attached. It would work for more than 3 columns inserted if the relevant code (selection.insert, i=i+1 )is copied