need help tweaking Debra Dalgleish macro
 

greetings, i pulled the following code from ms. dalgleish's data validation
file and would like the macro to stop once it reaches at certain row, if the
last number is continually overwritten that's fine.

i've tried if statements based on "target.row + 1" being less than a
numerical value, say 10, but that doesn't seem to work (if someone could clue
me into why that'd be great. any help appreciated, and props to ms.
dalgleish and her fab, i.e. incredibly useful, website =D
http://www.contextures.com/index.html


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo exitHandler

Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long

lCol = Target.Column 'column with data validation cell

If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 2, 3, 4, 5, 6
If Target.Offset(1, 0).Value = "" Then
lRow = Target.Row + 1

Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1

End If
Cells(lRow, lCol).Value = Target.Value
End Select

End If

exitHandler:
Application.EnableEvents = True

End Sub

need help tweaking Debra Dalgleish macro
 

Add a variable, lRowMax, which sets the maximum row number.

Dim lRowMax As Long
lRowMax = 10

Then add a code to test lRow:

If lRow lRowMax Then lRow = lRowMax
Cells(lRow, lCol + 1).Value = Target.Value


mwam423 wrote:
greetings, i pulled the following code from ms. dalgleish's data validation
file and would like the macro to stop once it reaches at certain row, if the
last number is continually overwritten that's fine.

i've tried if statements based on "target.row + 1" being less than a
numerical value, say 10, but that doesn't seem to work (if someone could clue
me into why that'd be great. any help appreciated, and props to ms.
dalgleish and her fab, i.e. incredibly useful, website =D
http://www.contextures.com/index.html


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo exitHandler

Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long

lCol = Target.Column 'column with data validation cell

If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 2, 3, 4, 5, 6
If Target.Offset(1, 0).Value = "" Then
lRow = Target.Row + 1

Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1

End If
Cells(lRow, lCol).Value = Target.Value
End Select

End If

exitHandler:
Application.EnableEvents = True

End Sub



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

need help tweaking Debra Dalgleish macro
 

hi debra, that does the trick, many thanks & happy mother's day!

p.s. fyi, adjusted your macro so that values, separated by rows, line up
directly under validation cell

"Debra Dalgleish" wrote:

Add a variable, lRowMax, which sets the maximum row number.

Dim lRowMax As Long
lRowMax = 10

Then add a code to test lRow:

If lRow lRowMax Then lRow = lRowMax
Cells(lRow, lCol + 1).Value = Target.Value

need help tweaking Debra Dalgleish macro
 

You're welcome, and thanks for letting me know how you changed the macro
to suit your workbook.

mwam423 wrote:
hi debra, that does the trick, many thanks & happy mother's day!

p.s. fyi, adjusted your macro so that values, separated by rows, line up
directly under validation cell

"Debra Dalgleish" wrote:


Add a variable, lRowMax, which sets the maximum row number.

Dim lRowMax As Long
lRowMax = 10

Then add a code to test lRow:

If lRow lRowMax Then lRow = lRowMax
Cells(lRow, lCol + 1).Value = Target.Value






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

need help tweaking Debra Dalgleish macro
 

hi debra, had another question if you're still lurking =D the code:

Select Case Target.Column
Case 2, 3, 4, 5, 6

specifies columns with validation cell. if i had numerous columns could i
use something like:
Case 2 to 50

and what if i wanted to make it open ended, or any column in the sheet?


"Debra Dalgleish" wrote:

You're welcome, and thanks for letting me know how you changed the macro
to suit your workbook.

need help tweaking Debra Dalgleish macro
 

nevermind, answered my own question!