Up and then down VB code
 

Hello all,
I have this bite of code that was writen for me. What do I need to change so
this works with text and not a number? Thanks

Sub pGarcia()
Dim evalCol As String, cl As Range
Dim lRow As Long, i As Long
'Enter the letter of the evaluation column
'Column letter "I" in the sheet you sent me
evalCol = "I"

'Enter the number of the row where the data starts
'Row "3" in the sheet you sent me
i = 3

'This identifies the last row in the spreadsheet
'It is just easier to use the GetLastRow function
'that it is to figure out if today's data contains
'more lines than yesterday's data
lRow = GetLastRow(ActiveSheet)

'Start the process
Do Until i lRow
With Cells(i, evalCol)
'Exits the loop early if the cells to the right
'and left are blank
If IsEmpty(.Offset(0, -1)) And _
IsEmpty(.Offset(0, 1)) Then Exit Do
.FormulaR1C1 = "= RC[-1]-RC[1]"
Select Case .Value
Case Is 0
.Value = "up"
Range(.Offset(0, 1), Cells(i, _
GetLastCol(ActiveSheet))).Insert Shift:=xlDown
lRow = lRow + 1
Case Is < 0
.Value = "down"
Range(Cells(i, 1), Cells(i, _
.Offset(0, -1).Column)).Insert Shift:=xlDown
lRow = lRow + 1
End Select
End With
i = i + 1
Loop
Range("I3").Select

End Sub

Up and then down VB code
 

Which part needs to work with Text??? Is it the Case statements which are
comparing Numbers or < 0???
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

Hello all,
I have this bite of code that was writen for me. What do I need to change so
this works with text and not a number? Thanks

Sub pGarcia()
Dim evalCol As String, cl As Range
Dim lRow As Long, i As Long
'Enter the letter of the evaluation column
'Column letter "I" in the sheet you sent me
evalCol = "I"

'Enter the number of the row where the data starts
'Row "3" in the sheet you sent me
i = 3

'This identifies the last row in the spreadsheet
'It is just easier to use the GetLastRow function
'that it is to figure out if today's data contains
'more lines than yesterday's data
lRow = GetLastRow(ActiveSheet)

'Start the process
Do Until i lRow
With Cells(i, evalCol)
'Exits the loop early if the cells to the right
'and left are blank
If IsEmpty(.Offset(0, -1)) And _
IsEmpty(.Offset(0, 1)) Then Exit Do
.FormulaR1C1 = "= RC[-1]-RC[1]"
Select Case .Value
Case Is 0
.Value = "up"
Range(.Offset(0, 1), Cells(i, _
GetLastCol(ActiveSheet))).Insert Shift:=xlDown
lRow = lRow + 1
Case Is < 0
.Value = "down"
Range(Cells(i, 1), Cells(i, _
.Offset(0, -1).Column)).Insert Shift:=xlDown
lRow = lRow + 1
End Select
End With
i = i + 1
Loop
Range("I3").Select

End Sub

Up and then down VB code
 

Yes. Instead of number I need to compare text.
e.g. dog = dog, true
dog = cat, false

"Jim Thomlinson" wrote:

Which part needs to work with Text??? Is it the Case statements which are
comparing Numbers or < 0???
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

Hello all,
I have this bite of code that was writen for me. What do I need to change so
this works with text and not a number? Thanks

Sub pGarcia()
Dim evalCol As String, cl As Range
Dim lRow As Long, i As Long
'Enter the letter of the evaluation column
'Column letter "I" in the sheet you sent me
evalCol = "I"

'Enter the number of the row where the data starts
'Row "3" in the sheet you sent me
i = 3

'This identifies the last row in the spreadsheet
'It is just easier to use the GetLastRow function
'that it is to figure out if today's data contains
'more lines than yesterday's data
lRow = GetLastRow(ActiveSheet)

'Start the process
Do Until i lRow
With Cells(i, evalCol)
'Exits the loop early if the cells to the right
'and left are blank
If IsEmpty(.Offset(0, -1)) And _
IsEmpty(.Offset(0, 1)) Then Exit Do
.FormulaR1C1 = "= RC[-1]-RC[1]"
Select Case .Value
Case Is 0
.Value = "up"
Range(.Offset(0, 1), Cells(i, _
GetLastCol(ActiveSheet))).Insert Shift:=xlDown
lRow = lRow + 1
Case Is < 0
.Value = "down"
Range(Cells(i, 1), Cells(i, _
.Offset(0, -1).Column)).Insert Shift:=xlDown
lRow = lRow + 1
End Select
End With
i = i + 1
Loop
Range("I3").Select

End Sub

Up and then down VB code
 

You know, some times the easy thing are over looked. I did a Vlookup. Thanks
for looking at the code for me.

"Jim Thomlinson" wrote:

Which part needs to work with Text??? Is it the Case statements which are
comparing Numbers or < 0???
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

Hello all,
I have this bite of code that was writen for me. What do I need to change so
this works with text and not a number? Thanks

Sub pGarcia()
Dim evalCol As String, cl As Range
Dim lRow As Long, i As Long
'Enter the letter of the evaluation column
'Column letter "I" in the sheet you sent me
evalCol = "I"

'Enter the number of the row where the data starts
'Row "3" in the sheet you sent me
i = 3

'This identifies the last row in the spreadsheet
'It is just easier to use the GetLastRow function
'that it is to figure out if today's data contains
'more lines than yesterday's data
lRow = GetLastRow(ActiveSheet)

'Start the process
Do Until i lRow
With Cells(i, evalCol)
'Exits the loop early if the cells to the right
'and left are blank
If IsEmpty(.Offset(0, -1)) And _
IsEmpty(.Offset(0, 1)) Then Exit Do
.FormulaR1C1 = "= RC[-1]-RC[1]"
Select Case .Value
Case Is 0
.Value = "up"
Range(.Offset(0, 1), Cells(i, _
GetLastCol(ActiveSheet))).Insert Shift:=xlDown
lRow = lRow + 1
Case Is < 0
.Value = "down"
Range(Cells(i, 1), Cells(i, _
.Offset(0, -1).Column)).Insert Shift:=xlDown
lRow = lRow + 1
End Select
End With
i = i + 1
Loop
Range("I3").Select

End Sub