Help with copy destination
 

I want to use the following to delete a UserForm designated name and then
copy a default value from another sheet into one of the resulting empty
cells:
Sub Delete_Member()
Dim Old_Member As String, cols As Long, rng As Range, c As Range
frmDelMem.Show
Old_Member = UCase$(frmDelMem.tbOldName.Text)
If Old_Member = "" Then Unload frmDelMem: Exit Sub
Unload frmDelMem
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(22, 1), Cells(22, 1).End(xlDown))
For Each c In rng
If c = Old_Member Then
c.EntireRow.SpecialCells(xlCellTypeConstants).Clea rContents
Sheets(3).Range("A1").Copy rng(c.Row, cols)'<-- here's the problem
End If
Next c
rng.Resize(, cols).Sort Key1:=rng, Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

It works to a point. The correct values in the correct row are deleted, but
the cell copied from sheets(3) winds up in the wrong place. For example, if
I designate a name in the UserForm that sits at A26, Row 26 is cleared, but
the range from sheets(3) winds up in E47 instead of E26.

If I use Sheets(3).Range("A1").Copy rng(c, cols), it winds up in E21
If I use Sheets(3).Range("A1").Copy rng(1, cols), it winds up in E22

Please fix it!

--
David

Help with copy destination
 

Sheets(3).Range("A1").Copy rng(cells(c.Row, cols))

Mike F
"David" wrote in message
...
I want to use the following to delete a UserForm designated name and then
copy a default value from another sheet into one of the resulting empty
cells:
Sub Delete_Member()
Dim Old_Member As String, cols As Long, rng As Range, c As Range
frmDelMem.Show
Old_Member = UCase$(frmDelMem.tbOldName.Text)
If Old_Member = "" Then Unload frmDelMem: Exit Sub
Unload frmDelMem
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(22, 1), Cells(22, 1).End(xlDown))
For Each c In rng
If c = Old_Member Then
c.EntireRow.SpecialCells(xlCellTypeConstants).Clea rContents
Sheets(3).Range("A1").Copy rng(c.Row, cols)'<-- here's the problem
End If
Next c
rng.Resize(, cols).Sort Key1:=rng, Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

It works to a point. The correct values in the correct row are deleted,
but
the cell copied from sheets(3) winds up in the wrong place. For example,
if
I designate a name in the UserForm that sits at A26, Row 26 is cleared,
but
the range from sheets(3) winds up in E47 instead of E26.

If I use Sheets(3).Range("A1").Copy rng(c, cols), it winds up in E21
If I use Sheets(3).Range("A1").Copy rng(1, cols), it winds up in E22

Please fix it!

--
David

Help with copy destination
 

Thanks for trying to help. I plugged in your offering and now the copied
range ends up in A21 :(

--
David

Mike Fogleman wrote

Sheets(3).Range("A1").Copy rng(cells(c.Row, cols))

Mike F
"David" wrote in message
...
I want to use the following to delete a UserForm designated name and
then
copy a default value from another sheet into one of the resulting
empty cells:
Sub Delete_Member()
Dim Old_Member As String, cols As Long, rng As Range, c As Range
frmDelMem.Show
Old_Member = UCase$(frmDelMem.tbOldName.Text)
If Old_Member = "" Then Unload frmDelMem: Exit Sub
Unload frmDelMem
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(22, 1), Cells(22, 1).End(xlDown))
For Each c In rng
If c = Old_Member Then
c.EntireRow.SpecialCells(xlCellTypeConstants).Clea rContents
Sheets(3).Range("A1").Copy rng(c.Row, cols)'<-- here's the problem
End If
Next c
rng.Resize(, cols).Sort Key1:=rng, Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

It works to a point. The correct values in the correct row are
deleted, but
the cell copied from sheets(3) winds up in the wrong place. For
example, if
I designate a name in the UserForm that sits at A26, Row 26 is
cleared, but
the range from sheets(3) winds up in E47 instead of E26.

If I use Sheets(3).Range("A1").Copy rng(c, cols), it winds up in E21
If I use Sheets(3).Range("A1").Copy rng(1, cols), it winds up in E22

Please fix it!

--
David

Help with copy destination
 

I kept tinkering.

Here's a line that works, keeping in mind I want Sheets(3).Range("A1") to
end up in Range(last column, same row) as the deleted name.

Sheets(3).Range("A1").Copy Range(Cells(c.Row, cols), Cells(c.Row, cols))

--
David

Mike Fogleman wrote

Sheets(3).Range("A1").Copy rng(cells(c.Row, cols))

Mike F
"David" wrote in message
...
I want to use the following to delete a UserForm designated name and
then
copy a default value from another sheet into one of the resulting
empty cells:
Sub Delete_Member()
Dim Old_Member As String, cols As Long, rng As Range, c As Range
frmDelMem.Show
Old_Member = UCase$(frmDelMem.tbOldName.Text)
If Old_Member = "" Then Unload frmDelMem: Exit Sub
Unload frmDelMem
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(22, 1), Cells(22, 1).End(xlDown))
For Each c In rng
If c = Old_Member Then
c.EntireRow.SpecialCells(xlCellTypeConstants).Clea rContents
Sheets(3).Range("A1").Copy rng(c.Row, cols)'<-- here's the problem
End If
Next c
rng.Resize(, cols).Sort Key1:=rng, Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

It works to a point. The correct values in the correct row are
deleted, but
the cell copied from sheets(3) winds up in the wrong place. For
example, if
I designate a name in the UserForm that sits at A26, Row 26 is
cleared, but
the range from sheets(3) winds up in E47 instead of E26.

If I use Sheets(3).Range("A1").Copy rng(c, cols), it winds up in E21
If I use Sheets(3).Range("A1").Copy rng(1, cols), it winds up in E22

Please fix it!

--
David