Loop until end

[Johnzea]

Is there a simpler way or loop to run through this code so I don't have to write this out so many times?

Any help would be much appreciated.....

Johnzea

Range("E59").Select
Selection.Value = Selection.Value
Range("E60").Select
Selection.Value = Selection.Value
Range("E61").Select
Selection.Value = Selection.Value
Range("E62").Select
Selection.Value = Selection.Value
Range("E63").Select
Selection.Value = Selection.Value
Range("E64").Select
Selection.Value = Selection.Value
Range("E65").Select
Selection.Value = Selection.Value
Range("E66").Select
Selection.Value = Selection.Value
Range("E67").Select
Selection.Value = Selection.Value
Range("E68").Select
Selection.Value = Selection.Value
Range("E69").Select
Selection.Value = Selection.Value
Range("E70").Select
Selection.Value = Selection.Value
Range("E71").Select
Selection.Value = Selection.Value
Range("E72").Select
Selection.Value = Selection.Value
Range("E73").Select
Selection.Value = Selection.Value
Range("E74").Select
Selection.Value = Selection.Value
Range("E75").Select
Selection.Value = Selection.Value
Range("E76").Select
Selection.Value = Selection.Value
Range("E77").Select
Selection.Value = Selection.Value
Range("E78").Select
Selection.Value = Selection.Value
Range("E79").Select
Selection.Value = Selection.Value
Range("E80").Select
Selection.Value = Selection.Value
Range("E81").Select
Selection.Value = Selection.Value
Range("E82").Select
Selection.Value = Selection.Value
Range("E83").Select
Selection.Value = Selection.Value
Range("E84").Select
Selection.Value = Selection.Value
Range("E85").Select
Selection.Value = Selection.Value
Range("E86").Select
Selection.Value = Selection.Value
Range("E87").Select
Selection.Value = Selection.Value
Range("E88").Select
Selection.Value = Selection.Value
Range("E89").Select
Selection.Value = Selection.Value
Range("E90").Select
Selection.Value = Selection.Value
Range("E91").Select
Selection.Value = Selection.Value
Range("E92").Select
Selection.Value = Selection.Value
Range("E93").Select
Selection.Value = Selection.Value
Range("E94").Select
Selection.Value = Selection.Value
Range("E95").Select
Selection.Value = Selection.Value
Range("E96").Select
Selection.Value = Selection.Value
Range("E97").Select
Selection.Value = Selection.Value
Range("E98").Select
Selection.Value = Selection.Value
Range("E99").Select
Selection.Value = Selection.Value
Range("E100").Select
Selection.Value = Selection.Value
Range("E101").Select
Selection.Value = Selection.Value
Range("E102").Select
Selection.Value = Selection.Value
Range("E103").Select
Selection.Value = Selection.Value
Range("E104").Select
Selection.Value = Selection.Value
Range("E105").Select
Selection.Value = Selection.Value
Range("E106").Select
Selection.Value = Selection.Value
Range("E107").Select
Selection.Value = Selection.Value
Range("E108").Select
Selection.Value = Selection.Value

 

[tyija1995]

Range("E59:E108").Select
Selection.Value = Selection.Value

I am assuming you are setting them to values instead of formulae?

 

[offthelip]

inarr =Range("E59:E108")
Range("E59:E108")=inarr

 

[Johnzea]

I need it to work like this...select "E59" then hit enter next select "E60" then hit enter next select "E61" then hit enter.....etc..... Hope I have that right?

 

[Rick Rothstein]

I have a question... what is your code supposed to be doing? It looks like you may simply be converting a text number to a real number... is that correct? If so, you can execute this single line of code (it replaces all of the code you posted)...

Range("E59:E108").TextToColumns

The above line of code assumes the range E59:E108 is already formatted as "General". If it is not, you must do this line of code first, then run the above line of code...

Range("E59:E108").NumberFormat = "General"

 

[Johnzea]

the E column cells have a formula so when you type someones initials in the cell it automatically fills in the rest of the row with the persons data from another sheet. my problem is when i paste initials in one of the E column cells nothing happens until I hit the enter key...I would like this to be automatic from E59:E108 as you paste the initials in cell.....Not sure if I have that right

 

[Norie]

What code are you using to automatically fill the rest of the row with the person's data?

 

[Johnzea]

Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Worksheets("SCHEDULE").Active
Set sh2 = Worksheets("MPL")
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("E:E")) Is Nothing Then
Set fn = sh2.Range("C:C").Find(Target.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
Target.Offset(, 4) = fn.Offset(, 4).Value
Target.Offset(, 2).Resize(, 7) = fn.Offset(, 2).Resize(, 7).Value
End If
End If

 

[Norie]

Is that code in a the worksheet Change event or the SelectionChange event?

If it's in the latter try changing it to the Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range

    Set sh1 = Worksheets("SCHEDULE")
    Set sh2 = Worksheets("MPL")
    
    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Set fn = sh2.Range("C:C").Find(Target.Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            Target.Offset(, 4) = fn.Offset(, 4).Value
            Target.Offset(, 2).Resize(, 7) = fn.Offset(, 2).Resize(, 7).Value
        End If
    End If
    
End Sub

 

[Johnzea]

yup I had it in the latter...is working now. TY! I really thought is was the initial coding that was the problem.. TY again


TY to all!