Posts by excedrin1997

    Dealing with time values in Excel is a nightmare!! Hopefully someone has an answer for this, how can you determine is a cell contains a time? To simplify things, lets say I want to check cells C5:C15 to see if either contains a time value, if so, simply return a "Yes". Obviously, the code below does not work or I would not be requesting help! It just returns "Yes" for any value. C5:C15 can contain time or text, so I would like to differentiate this. Any help would be appreciated!


    [VBA]


    Sub CheckforTime()


    Dim rCell As Range
    Dim myRange As Range
    Dim x As Integer


    Set myRange = Range("C5:C15")


    For Each rCell In myRange

    If rCell.Value > TimeValue("00:00:00") Then
    MsgBox "YES"
    Else
    MsgBox "NO"
    x = x + 1
    End If
    Next


    End Sub


    [/VBA]

    Thanks for your reply, reading it back I guess it can be a little confusing, and yes, it should have started with Range ("A150"). There is a list of text items in Column Z from 7 to 40, which some contain blanks. Using a Macro, it goes through the column and puts them in a range (beginning with A150) working upwards but keeping the same order as Column Z excluding the blanks. Both x and j are 0, and using x = x - 1 allows me to go backwards, but maybe it is redundant. What I am trying to figure out is how to also put in a text word in Column C if a value has been entered into Column A from the statement.

    Hello,


    I have a code that will go through a specified range and create a list of those values excluding any blanks. What I want to do is also add a value 2 columns to the right of this list. In the code below, it will go backwards from row 40 to row 7 of column Z, and if it is not empty, add that value to Range("A150") and move upward while keeping the order the same. I would like to simply add the same text value (such as "Done") to column C beside each new entry, but this code has always been confusing to me. Any help would be appreciated.


    [VBA]


    Dim i As Integer, x As Integer, j As Integer


    For i = 40 To 7 Step -1


    If (Cells(i, "Z") <> "") Then
    Cells(151, "A").Offset(x, j) = Cells(i, "Z")
    x= x- 1
    End If
    Next


    [/VBA]

    I have 4 columns where I am trying to use conditional formatting on based on the value of the adjacent cell. For instance, if value in column B equals "text string" then cell in same row in column H has the conditional formatting applied. Better yet, if possible, if column B equals "text string" then Column B and H have the conditional formatting applied. The same goes for columns K and Q, S and Z, and AC and AI. Thanks.

    I'm having issues with correctly looping through columns and rows replacing a certain text with text from the second column. I first tried to make a formula linked with the cell value in column B could not get anywhere with that. Then I tried using macro to replace the text, but the closest I have come is replacing only a specific row instead of every row. I do not know how to code to make it loop through rows and columns or the selected range. I know either would work as I have figured it out a few years ago but lost the code. This code below will replaced every instance in the sheet, rather than a certain column. I would much rather have a formula where it will just update when I put a different value in the second column. To make things easier, I just copy down the same formulas for each row, so I just have to replace one value, but have the correct formula. Here is the code that replaces every instance in the sheet.


    Any help would be greatly appreciated.

    Hello,


    I am scratching my head a bit on this, and I know this is probably easy. I am running 4 macros based on a button click:

    Code
    Sub CallmyCode()
    
    
    Call Macro1
    Call Macro2
    Call Macro3
    Call Macro4
    
    
    End Sub


    What I am trying to do is not run the consecutive macros if a condition is met in the first macro, which looks like this:

    Code
    Sub macro1()
    If Range("B1") = "certain_string" Then
    MsgBox "You clicked the wrong button."
    Exit Sub
    End If
    End Sub


    Any help would be appreciated.

    Re: Replace text in rows with value from column


    Works wonderfully! Thanks :)



    Quote from skywriter;768876

    Try this. :cool:

    When my computer crashed, I lost the code that I used to replace all values in a row with the value from the first column. For instance, I have a sheet with Column A being a persons name. Columns B:AB each contain a formula, and I want to replace a value in that formula with the value from Column A. I was able to recollect the code below, but what happens is that it replace every value I want to replace with the value from the first cell of the first column. I know this is a step in the right direction, but I recall developing this code even farther, so maybe I am not going about it the right way. Hopefully someone can be of some assistance, which would be much appreciated and save me mountains of time.


    Code
    Sub FindValueAndReplace()
    Dim myList, mySearchRange
    Set myList = Sheets("Sick Leave").Range("A5:A99")
    Set mySearchRange = Sheets("Sick Leave").Range("B5:AB99")
    For Each cel In myList
    mySearchRange.Replace What:="ValueToReplace", Replacement:=cel.Value
    Next cel
    End Sub

    I am using some coding to change text colors instead of conditional formatting due to users always copying and pasting where they are not supposed to. The problem I have is that I have a simple few lines that resets the cell back to a certain color, which works when the backspace is used. When the delete key is used, it does not trigger the worksheet change event, therefore, the code is not run. Any ideas? Thanks in advance!!


    Re: Reselect selected range after macro has run


    Sorry for being so vague, let me throw in a few more pieces of code...


    I have this code in a Sheet_Change event, so what when I type "AD" into a cell, for example, it loops through the range, therefore, changing my selection. I am working on changing the way it checks for "AD" by using a Select Case, i'm working if it will still change my selection?