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]

Works perfectly, thanks!

Attached is a quick example. Click Add, and it will run the code. If a value is entered into Column A, I would like to enter a value such as "Yes" in Column C. I can only accomplish the the last row. And thanks for any help!

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]

Re: Conditional Formatting on cell based on value froM another cell

Both solutions work wonderfully, thanks!

Re: Conditional Formatting on cell based on value froM another cell

Quote from Carim;796204

Why don't you attach a sample workbook for a tailor-made suggestion ...

Attached is an example. I am trying to run conditional formatting on the adjacent cells that received the formatting. Thanks!

Re: Conditional Formatting on cell based on value froM another cell

When I say "text string" it is a specific phrase in that text that I am looking for.

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.

Sub FindReplaceAll()




Dim fnd As Variant
Dim rplc As Variant
Dim lcol As Long
Dim lastCol As Long




fnd = "first sheet"
rplc = Cells(1, 2).Value




lastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column




For lcol = 3 To lastCol




  Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next lcol




End Sub

Any help would be greatly appreciated.

Re: If condition met in first macro, do not run the rest

Thanks, I appreciate it a lot! I am always afraid to simplify too much and then lose necessary code.

Re: If condition met in first macro, do not run the rest

Small sample is attached, simplified it the best I could so it would fit, and with my original code before I tried your solutions. Thanks for the help!

Re: If condition met in first macro, do not run the rest

Thanks for the help. I tried the code, but it exits the Sub whether bMacroTest is True or False. The problem I had before it that it would run all 4 macros regardless, now it just runs the first.

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:

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:

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:

Code

Sub FindValueAndReplace()
Dim rngData As Range, r As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("BALANCES").Range("A1").CurrentRegion
    Set rngData = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
    For Each r In rngData.Rows
        r.Replace What:="=*!", Replacement:="=" & Cells(r.Row, 1).Value & "!"
    Next r
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Display More

Re: Replace text in rows with value from column

It is easier to copy the top line in the last tab down, then replace the one text string with the first name in the column. Thanks for your help!

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.

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

Re: Delete Key does not trigger Worksheet_Change

Each of the cells are merged, so I'm guessing that makes a difference? How do I get around this?

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!!

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub
Dim dupColorRed As Long
Dim dupColorReg As Long
dupColorRed = RGB(Red:=255, Green:=0, Blue:=0)
dupColorReg = RGB(Red:=0, Green:=0, Blue:=0)
If Target.Value = "" Then        ' if cell is blank it resets the color back to black and no bold
    Target.Font.Color = dupColorReg
    Target.Font.Bold = False
    Exit Sub
End If
If Target.Value = "Closed" Then     '   Used multiple times throughout
    Target.Font.Color = dupColorReg
    Target.Font.Bold = False
Exit Sub
End If
If Target.Value = "Collapsed" Then     '   Used multiple times throughout
    Target.Font.Color = dupColorReg
    Target.Font.Bold = False
Exit Sub
End If
Dim F As Long
Dim KeyCells As Range
    Set KeyCells = Range("X8:AS77, CG8:DB73, X8:AS126, B128:EV152, B156:W182, AG156:BB182, BL156:CG182, CQ156:DL182")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
With Target
    F = Application.WorksheetFunction.CountIf(Range("FB9:FE86"), Target.Value)    '  makes duplicates red and bold
    Select Case F
    Case Is >= 1
    .Font.Color = dupColorReg
    .Font.Bold = False
    Case Is < 1
    .Font.Color = dupColorRed
    .Font.Bold = True
    End Select
End With
    End If
If Target.Value Like "*" & Chr(32) & "*" & Chr(32) & Chr(42) & "*" Then  ' excludes use of specific value
End If
Exit Sub
If Target.Value Like "*" & Chr(32) & "*" & Chr(32) & Chr(169) & Chr(32) & "*" Then  ' excludes use of specific value
Exit Sub
End If


End Sub

Re: Reselect selected range after macro has run

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

[COLOR=#0000ff]Dim[/COLOR] ActSheet [COLOR=#0000ff]As[/COLOR] Worksheet 
[COLOR=#0000ff]Dim[/COLOR] SelRange [COLOR=#0000ff]As[/COLOR] Range 
 
[COLOR=#0000ff]Set[/COLOR] ActSheet = ActiveSheet 
[COLOR=#0000ff]Set[/COLOR] SelRange = Selection   [COLOR=#006400]'[/COLOR]
 
Set Rng = Range("B4:BC59")
For Each Row In Rng.Rows
    For Each Cell In Row.Cells
            If Cell.Value = "AD" Then
            Cell.Offset(0, 1).Font.Color = -16776961
            End If
Next Cell
Next Row


ActSheet.Select 
SelRange.Select 
Selection.Copy

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?