Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using copy and destination formatting | Excel Discussion (Misc queries) | |||
VBA .copy destination | Excel Discussion (Misc queries) | |||
copy destination values | Excel Programming | |||
Range.Copy (Destination) | Excel Programming | |||
Selection.Copy Destination:= | Excel Programming |