Posts by yjoshi

    I am really surprised that this has remained untouched for so long!!! :o

    Are you talking about the same file or different files created diffetently???

    Is it on NT/2000 (actually should not make any difference :()

    Hi Andy,

    Welcome to the board, and with this first very interesting quote

    I think i am understanding your problem a bit (just a bit)

    You are creating an application, suing formulas (and not using VBA) very hard Andy

    This will certainly require VBA.

    If you can post a sample of what you have created, and also in the same sheet, list down all the things that you want to do, i will certainly try to help you.

    If i try to start doing something now, it will be shooting in dark.

    Please update, and help us to help you :cheers:

    Tried my hard,

    I thought taking the total of all the cells to 288 will help, but now it puts value 4.5 everywhere, violating the integer function.

    I am also struggling with one quote which required a problem to be solved in integer mode :(

    Will the conditional formatting sufficient???

    here is the formula for conditional formatting:

    I have also attached a worksheet.

    Let us know if you need something different and also if you need some kind of script in VBA to do this job for you.

    This is something that came to me naturally as i read your post.

    Am i right in judging it???:beergrin:

    I think you are struggling with $ sign which is inserted when you work with conditional formattin and select the range with mouse click.

    E.G. IF you select A1 and go to conditional formatting and click on A1 to get the formula, it will enter "=$A$1>1" in the formula.

    Now if you copy the format to 50 rows, all will be true or false based on A1.

    To come over this, change the formula to "=A1>1" and then copy.

    Or even, select fifty cells and go to conditional formatting with A1 as primary selection and enter the formula above, the formating will be made relative to the each cell as per your requirement.

    Hope this works. Let me know if not. :cheers:

    Hi Casca,

    Just attach a sample copy of the file.

    Somehow i get a feeling that you want to save only one sheet and not the entire workbook (which can contain a lot of other data)

    If the entire workbook is to be saved with different name, i believe, there will be lot of duplication.

    ALSO:: Be careful: not to make too many copies of same data!!! can be pretty difficult for the management to trace the correct information. :nono:

    Hi Howdy Honey,

    Welcome d-board!!!:thumbup:

    Please be a bit more specific in your posts.

    Do you want to protect the cells based on some condition (value, format, position?) or specific ones???

    If specific, then NateO's suggestion is excellent. If you want this to be done by way of some code, please update accordingly.

    Hi Colo, good one.

    I have different approach.

    Say if you want to have a summary of colored cells in the worksheets!!!
    Then both the inputs should be ranges.

    Public Function Cnt_Color(ByVal which_color As Range, ByVal rng As Range) As Long

    Dim lngCnt As Long, c As Range

    For Each c In rng
    If c.Interior.ColorIndex = which_color.Interior.ColorIndex _
    Then lngCnt = lngCnt + 1

    Cnt_Color = lngCnt

    End Function

    Also attached a sheet!!!

    Note: Once you have pasted a function in a module as Public, it can be accessed from Fx which will also give you what parameters to pass. :thumbup:

    Very confusing in deed.

    The formula will remain the same.

    IF you want to use some logical condition, please change the figure 24 instead of your logical formula.

    1. You will have a base date
    2. You know how many months to increase
    3. Why you want to increase (major / minor) you can set a if formula.

    I can help you with if formula, but not sure what you want.

    May be, if this is getting tooooo much for you, send an email or catch me on AOL, (note that i am in india, so see times)

    Thats all i can do, but surely wish to help you.

    I take liberty to paste a long code here.

    As per the requirement of perryco sent by U2U, he is handling 40 vehicles and the data needs to be entered in the appropriate sheets based on value entered in col D.

    The following code will :
    1. See if the value in col D is valid (1 to 40)
    2. See if the required sheet is available?
    3. If not add the sheet from sample sheet
    4. Copy the data in appropriat place
    5. Put a date time stamp in front of the data

    Private Sub Worksheet_Change(ByVal Target As Range)
    'This code will check the value in D, based on the number
    'will paste the data in the sheet pertaining to it.
    Dim sht As String
    Dim s As Worksheet
    Dim avialable As Boolean
    Dim decision As VbMsgBoxResult
    avialable = False

    '========= Part I : checking range and data entered =========
    'This will check if the value changed is in D column
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

    'This will check if the value is numeric and within the range of 1 to 40

    If Target.Count <> 1 Then
    MsgBox "The changed cells are more than one, can handle one at a time :("
    Exit Sub
    End If

    If IsNumeric(Target.Value) = False Or _
    Target.Value < 1 Or _
    Target.Value &gt; 40 Then Exit Sub

    'This will create the sheet name to copy the data
    sht = "Veh" & Target.Value

    '========= Part II : checking if the sheet exists =========
    'This will check if the sheet is available
    For Each s In ActiveWorkbook.Sheets
    If s.Name = sht Then avialable = True

    'This will create the sheet if not available and name as per the vehicle #
    If avialable = False Then

    decision = MsgBox("The Sheet " & sht & _
    " is not available, do you want to add it?", vbYesNo)
    If decision = vbNo Then Exit Sub

    Sheets("sample").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = sht
    avialable = True
    End If

    '========= Part III : Actual copy-paste and time stamping =========
    Application.ScreenUpdating = False
    With Sheets(sht)
    .Range("A65536").End(xlUp).Offset(1, 0).Select
    Application.CutCopyMode = False
    End With
    Target.Offset(0, 1).Value = "The record is added in Sheet : " & sht & " on : " & Now()
    Target.Offset(0, -3).Select
    Application.ScreenUpdating = True

    End Sub


    Thanx to perryco for asking cristle clear question. This is one of really good codes i wrote for some time.

    Anyone please take liberty to jump in and suggest changes

    Also a file attached

    Hi Casca,

    Welcome to the board

    This is what you want.

    This code needs to be put in the Code for his sheet

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("E2:E100")) Is Nothing Then Exit Sub
        If Target.Value = "BK" Or Target.Value = "NA" Or Target.Value = "AM" _
           Or Target.Value = "SD" Then
        Target.Offset(0, 1).Value = Now()
        End If
    End Sub

    also attached the sheet for your help.


    :thumbup: Well said Dave.

    I knew, but was lazy to retpe the code, and knew that you will give atleast one more alternative :eureka:

    Mae: Don't be aftead of the code.
    See, if you need to do things effeciently, VBA, that's the highway. It takes a bit of time to reach there, but once you are there, your speed will be really good.

    Just right on the sheet in which you want to keep this autocorrect.
    In the code window for this sheet, just paste the code, and it will work.

    Regarding Formulas, yes it is very much possible.

    Just paste
    =IF(A1= "computer key 100","CH100","")
    in cell B1 and be sure that it will not be autofilled.

    See now you have 2-3 solutions, it depends on you what you are comfortable with :)


    Sorry to jump in your code,

    but what has happened is that, the autocorrect works every-wher except A1 to A100, but he wants that it should work only for A1 to A100 ???

    This way, will he be required to give range of all the cells to exclude, which can be quite nasty. In this case, he will have to give union("a101:A65536","B:IV")

    What do you say???

    Hi Roy,

    I had suggested it as an alternative!!
    (i know not very good, but was not sure of what he wants)

    What i can guess by interactions is that,

    he has one sheet on which there is some data in auto filter mode with some data hidden.

    He wants to copy it to other sheet, including the hidden data and keep the filter live.

    What i will suggest is that::
    - copy the filter information in the variables in run
    - deavtivate the filter
    - copy and paste the data
    - apply the filter back to the data as well as the copied data.

    how do you think this about efficiency???