Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:


Range("C8").Select
Selection.Copy
Range("C17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False



Range("G8").Select
Selection.Copy
Range("E17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Thank you for any help.

Drop the copy|paste special|values and just assign the value.

Dim FromCell As Range
Dim DestCell As Range

With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=FromCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If DestCell Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
End If
End With



Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
Selection.Copy
Range("C17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("G8").Select
Selection.Copy
Range("E17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

* * Dim FromCell As Range
* * Dim DestCell As Range

* * With ActiveSheet
* * * * Set FromCell = .Range("c8")
* * * * Set DestCell = .Cells.Find(What:="", _
* * * * * * * * * * * * * After:=FromCell, _
* * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * Searchorder:=xlByColumns, _
* * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * SearchFormat:=False)

* * * * If DestCell Is Nothing Then
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * End If
* * End With





Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
* * Selection.Copy
* * Range("C17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False

* * Range("G8").Select
* * Selection.Copy
* * Range("E17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Dave

Thank you... I wil give it a try...


/Kasper

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

* * Dim FromCell As Range
* * Dim DestCell As Range

* * With ActiveSheet
* * * * Set FromCell = .Range("c8")
* * * * Set DestCell = .Cells.Find(What:="", _
* * * * * * * * * * * * * After:=FromCell, _
* * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * Searchorder:=xlByColumns, _
* * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * SearchFormat:=False)

* * * * If DestCell Is Nothing Then
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * End If
* * End With





Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
* * Selection.Copy
* * Range("C17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False

* * Range("G8").Select
* * Selection.Copy
* * Range("E17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

I do have one objection... I need the macro to start searching after
an empty cell from C17 and down, not directly under "FromCell". How do
I do this?

After:=.range("C17"), _

(I tried to match what your posted code did <bg.)

Kasper wrote:
<<snipped
I do have one objection... I need the macro to start searching after
an empty cell from C17 and down, not directly under "FromCell". How do
I do this?

--

Dave Peterson

I tried to match what your code did, but I didn't do it right!

Kasper wrote:

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

Dim FromCell As Range
Dim DestCell As Range

With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=FromCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If DestCell Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
End If
End With





Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
Selection.Copy
Range("C17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("G8").Select
Selection.Copy
Range("E17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

I do have one objection... I need the macro to start searching after
an empty cell from C17 and down, not directly under "FromCell". How do
I do this?

--

Dave Peterson

On 25 Feb., 14:55, Dave Peterson wrote:
I tried to match what your code did, but I didn't do it right!





Kasper wrote:

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

* * Dim FromCell As Range
* * Dim DestCell As Range

* * With ActiveSheet
* * * * Set FromCell = .Range("c8")
* * * * Set DestCell = .Cells.Find(What:="", _
* * * * * * * * * * * * * After:=FromCell, _
* * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * Searchorder:=xlByColumns, _
* * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * SearchFormat:=False)

* * * * If DestCell Is Nothing Then
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * End If
* * End With

Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.

The macro ended up being:

Dim FromCell As Range
Dim DestCell As Range


With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=.Range("C17"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set FromCell2 = .Range("G8")
Set DestCell2 = .Cells.Find(What:="", _
After:=.Range("E17"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


If DestCell Or DestCell2 Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
DestCell2.Value = FromCell2.Value
End If
End With


Although the "If DestCell Or DestCell2 Is Nothing Then" doesn't seem
to stop the macro. It runs through the "else" part anyway.

Although the "If DestCell Or DestCell2 Is Nothing Then" doesn't seem
to stop the macro. It runs through the "else" part anyway.

Changed "Destcell" to "Fromcell" and even if I leave it blank it still
runs through with the "else" part???

Check it this way:

If DestCell is nothing _
Or DestCell2 Is Nothing Then
'do nothing
Else

Kasper wrote:

The macro ended up being:

Dim FromCell As Range
Dim DestCell As Range

With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=.Range("C17"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set FromCell2 = .Range("G8")
Set DestCell2 = .Cells.Find(What:="", _
After:=.Range("E17"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If DestCell Or DestCell2 Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
DestCell2.Value = FromCell2.Value
End If
End With

Although the "If DestCell Or DestCell2 Is Nothing Then" doesn't seem
to stop the macro. It runs through the "else" part anyway.

--

Dave Peterson

Although the "If DestCell Or DestCell2 Is Nothing Then" doesn't seem
to stop the macro. It runs through the "else" part anyway.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Well it doesn't work as I intended... If i fill out either DestCell og
DestCell2 it still copies the one value. Is there a way to stop the
macro if either is empty.
I tried Exit Sub but that doesn't seem to work.

Don't know if this is relevant but FromCell and Fromcell2 is a merged
cell.

Tried this:

If FromCell Is Nothing _
Or FromCell2 Is Nothing Then Exit Sub
'do nothing
Else
DestCell.Value = FromCell.Value
DestCell2.Value = FromCell2.Value
End If.

But this Gives an error: Compile error - ELSE without IF?

FromCell and FromCell2 are always going to be assigned to ranges. That's what
these two lines do:

Set FromCell = .Range("c8")
Set FromCell2 = .Range("G8")

If you want to check to see if the either destcell or destcell2 is empty:

if destcell.value = "" _
or destcell2.value = "" then
'do nothing
else
.....



Kasper wrote:

Tried this:

If FromCell Is Nothing _
Or FromCell2 Is Nothing Then Exit Sub
'do nothing
Else
DestCell.Value = FromCell.Value
DestCell2.Value = FromCell2.Value
End If.

But this Gives an error: Compile error - ELSE without IF?

--

Dave Peterson

On 26 Feb., 14:01, Dave Peterson wrote:
FromCell and FromCell2 are always going to be assigned to ranges. *That's what
these two lines do:

* * * * Set FromCell = .Range("c8")
* * * * Set FromCell2 = .Range("G8")

If you want to check to see if the either destcell or destcell2 is empty:

* *if destcell.value = "" _
* * *or destcell2.value = "" then
* * * *'do nothing
* *else
* * * .....

Kasper wrote:

Tried this:

*If FromCell Is Nothing _
* * * * Or FromCell2 Is Nothing Then Exit Sub
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * * * DestCell2.Value = FromCell2.Value
* * * * End If.

But this Gives an error: Compile error - ELSE without IF?

--

Dave Peterson

It works as intended.

Thank your for your time and help.

/Kasper