VBA help

junebug

Board Regular
Joined
Apr 4, 2003
Messages
110
I have an attendance sheet that has point values in column E. What I need is so code will start at the bottom of sheet (row 1000) and look up a row at a time until it finds a row in column E that is > zero. The reason it needs to start at row 1000 is that the sheet is sorted newest occurance to oldest and I am looking for the oldest occurence that has a point value. Not all occurences have a point value. If they don't they will have a zero in column E.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Does this code suit your needs?


Code:
Sub Macro1()
For MY_ROWS = 1000 To 1 Step -1
    If Range("E" & MY_ROWS).Value > 0 Then
        'YOUR CODE HERE
    End If
Next MY_ROWS
End Sub
 

junebug

Board Regular
Joined
Apr 4, 2003
Messages
110
It is not working and I don't know why. It could be my part of the code. I want it to find the first cell that has points starting from the bottom and then select that cell and copy it and put it in cell f3. The column it is looking in with the points are actually formulas that show a point value if that matters. IE: cell e899 will show number 6 but the cell is actually a formula.
 

junebug

Board Regular
Joined
Apr 4, 2003
Messages
110

ADVERTISEMENT

Sub Macro10()
Range("E:E").Select
For MY_ROWS = 1000 To 1 Step -1
If Range("E" & MY_ROWS).Value > 0 Then
ActiveCell.Copy
Range("F3").Select
ActiveCell.PasteSpecial xlPasteAll
End If
Next MY_ROWS
End Sub
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

This amended code should work

Code:
Range("E:E").Select
For MY_ROWS = 1000 To 1 Step -1
If Range("E" & MY_ROWS).Value > 0 Then
Range("E" & MY_ROWS).Copy
Range("F3").Select
    Selection.PasteSpecial Paste:=xlValues
End If
Next MY_ROWS
 

junebug

Board Regular
Joined
Apr 4, 2003
Messages
110

ADVERTISEMENT

Almost working except where column e may have
E14 2
E15 6
E16 8
E17 0

It is choosing 2 instead of 8. Is it maybe going from top down instead of bottom up?
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

No the code continues to row 1.

If you want to stop the code after the first value >0 is copied then use this code.

Code:
Range("E:E").Select 
For MY_ROWS = 1000 To 1 Step -1 
If Range("E" & MY_ROWS).Value > 0 Then 
Range("E" & MY_ROWS).Copy 
Range("F3").Select 
    Selection.PasteSpecial Paste:=xlValues
    End
End If 
Next MY_ROWS
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top