Excel 2010 Defining Object as Range of Values

[NeedyHelpExcelMan]

Hello all,

So I am trying to have a bit of code that recognizes all values between 3 and 5 within a range (which I will refer to as n) and if a cell value falls within this range then apply the replace the value with the following formula (n[original val] - 3) x .5) +3

Below is the code I have thus far to recognize all values between 3 and 5 but I'm having trouble defining the values - not sure whether to dim a range or something else?

Thanks in advance

For Each Cel In Range(Range("E1").End(xlDown).End(xlDown), Range("E" & Rows.Count))
    If Cel.value is >= 3 & <= 5 Then Cel.Offset(0, 0).Value = ((Cel.value - 3)*.5)+3
    
Next

 

[wigi]

Hi

Like this:

For Each Cel In Intersect(Range(Range("E1").End(xlDown).End(xlDown), Range("E" & Rows.Count)), ActiveSheet.UsedRange)
    If Cel.Value >= 3 And Cel.Value <= 5 Then Cel.Value = ((Cel.Value - 3) * 0.5) + 3
Next

Please note that I also use the UsedRange of the sheet to restrict the range to what is useful.

 

[NeedyHelpExcelMan]

Thank you kindly!

 

[NeedyHelpExcelMan]

wigi,

Hello again, using a similar piece of code as above I was wondering if you would be able to help me yet again.
I first want to search a range within column F for a value, if a value does exist, I want to copy and paste that value to the corresponding cell in column E. Code is

For Each Cel In Range(Range("E1").End(xlDown).End(xlDown).End(xlDown).End(xlDown).Offset(0, 1), Range("F" & Rows.Count))
If Cel.Value <> "" Then Cel.Copy.Offset(-1, 0).PasteSpecial (xlPasteValues)
Next

Followed by searching the same range within column F and if a value exists, selecting and copying the entire row and pasting it at the top of the workbook (which has headers, so data begins in row 2). Code is as follows

For Each Cel In Range(Range("E1").End(xlDown).End(xlDown).End(xlDown).End(xlDown).Offset(0, 1), Range("F" & Rows.Count))
    If Cel.Value <> "" Then Cel.EntireRow.Select.Copy.Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
Next

I'm a bit stuck though in implementing the code so that it does what I want - any suggestions? Thanks in advance

 

[wigi]

Hello

Why do you ignore my advice to use the Intersect with ActiveSheet.UsedRange (see previous coding)?

For the first code:

For Each Cel In Intersect(Range(Range("E1").End(xlDown).End(xlDown).End(xlDown).End(xlDown).Offset(0, 1), Range("F" & Rows.Count)), ActiveSheet.UsedRange)   If Cel.Value <> "" Then Cel.Offset(0,-1).Value = Cel.Value
Next

Quite different from what you had.

For the second code, when inserting or deleting rows/columns, loop backwards ! From the last row to the first. If not, you can be missing out on some rows/columns as they shift.

Also, please have a look at other topics. Code like this will be generate errors:

Cel.EntireRow.Select.Copy.Rows("2:2").Select

Did you loop through your code with F8?

 

[NeedyHelpExcelMan]

wigi,

I made the adjustments using the Intersect with ActiveSheet.UsedRange - thank you

For the loop, this is what I have so far... however, it is not working

Sub test()
Z = 0
With Range(Range("E1").End(xlDown).End(xlDown).End(xlDown).End(xlDown).Offset(0, 1), Range("F" & Rows.Count))
    Set c = .Find(Cel.Value <> "", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.EntireRow.Copy Destination:=Cells(2 + Z, 1)
            Z = Z + 1
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

Any thoughts?

 

[wigi]

Any thoughts?

Rather than guessing what the syntax might be, it would be helpful to consult other topic, or internet, or a book on VBA. This kind of guessing won't be the solution.

You said you used ActiveSheet.UsedRange. Why don't I see this in your code?

A simple code example on looping BACKWARDS through a range:

Sub Loop_through_cells()

    Dim lRow As Long

    For lRow = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        
        'blah blah blah
        
    Next

End Sub

I advised you to loop backwards in the previous post.