Find Value In Range & Change Value In Another

002635 · Dec 6th 2006

Hi,

First off, thanks to all who have provided to this forum and helped me with my various spreadsheets over the years. I have looked for the answer to this problem on many forums and can't seem to find it, maybe I am just looking for the wrong thing....

In VBA I am trying to search a Range in my worksheet for a specfic value (for arguments sake let's say todays date). When it finds an occurrence of todays date, I want my marco to insert a "1" into another Range on the same Row as the occurence of todays date i.e. if VBA finds todays date on Row 26 of Coulmn F, I want Row 26 of Column T to have a "1" in it. I have defined both my ranges as the same size (in the is case my search range is cells 5-500 in Coulmn F and my output range is 5-500 in Column T).

Can anyone help me please?

Thanks In Advance

Imran

**royUK** · Dec 6th 2006

Re: Find Value In One Range And Change Value In Another Range

Like this?

Code

Dim MyRange As Range
  Dim cl As Range
  
     Set MyRange = Range("a1", Range("a65536").End(xlUp))
For Each cl In MyRange
If cl.Value = Date Then cl.Offset(0, 19) = 1
Next cl

002635 · Dec 6th 2006

Re: Find Value In One Range And Change Value In Another Range

Thanks Roy

I have used that method before, but when my range becomes large (30,000 rows) I find that it takes a long time to run the code. I was hoping to find a quicker solution. I have been experimenting with Find (which seems to be faster) but can't get the row reference transferred into my other range.

Any thoughts?

Thanks

Imran

**royUK** · Dec 6th 2006

Re: Find Value In One Range And Change Value In Another Range

What code have you tried?

002635 · Dec 6th 2006

Re: Find Value In One Range And Change Value In Another Range

Sorry for the delay Roy, got tied up doing other things.

Have achieved what I wanted to do with an amalgamtion of code found on the forum. FYI this is what I have used......any improvements are always welcome:

Code

Sub Change_Weight()
Dim TopCell As Range, BttmCell As Range, SrcRange As Range, TgtRange As Range
Dim x As Integer, i As Integer, numofRows As Integer
Dim Today1 As Date


Today1 = Application.Worksheets("Sheet1").Range("Trading_Day")
Set TopCell = Application.Worksheets("Sheet1").Range("F6")
Set BttmCell = Application.Worksheets("Sheet1").Range("F65536").End(xlUp)


If TopCell.Row > BttmCell.Row Then End


Set SrcRange = Range(TopCell, BttmCell)


Set TopCell = Application.Worksheets("Sheet1").Range("T6")
Set BttmCell = Application.Worksheets("Sheet1").Range("T65536").End(xlUp)


Set TgtRange = Range(TopCell, BttmCell)


numofRows = SrcRange.Rows.Count


For i = 1 To numofRows
    If SrcRange(i) = Today1 Then
        TgtRange(i) = "1"
    End If
Next i


End Sub

Display More

JMAN · Dec 6th 2006

Re: Find Value In One Range And Change Value In Another Range

Maybe something like this?

Code

Sub FilterStuff()


    Dim rFilter As Range, m As Range
    
    With Sheet1
        Set rFilter = .Range("A1:D1")
        Set m = .Range("D2:D" & .Range("A65536").End(xlUp).Row)
        m = "=IF(A2=TODAY(),TRUE,FALSE)"
    End With
    
    With rFilter
        .AutoFilter
        .AutoFilter field:=4, Criteria1:="TRUE"
        .Offset(1, 2).Resize(.CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Value = 1
        .AutoFilter
    End With
    
End Sub

Display More

Find Value In Range & Change Value In Another

Participate now!

Share