Dim a range query

[hatstand]

I have wrote the following to look down column A until it finds a match with ‘FindDate’ (a named range that holds the date I wish to find). It then pastes ‘InserThis’ (a named range with word I would like inserted). This is offset by 22 columns. It works fine if ‘InsertThis’ is only one cell.

Is it possible make ‘InsertThis’ a range or cells, so more than one cell is pasted when a match is found?

Sub TestInsertOne()
Dim ArrayResult As Range
Set ArrayResult = Range("InsertThis")
'Start from cell.
Range("A3").Select
'Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
'How many cells to concatenate and what range to equal.
If ActiveCell = Range("FindDate") Then
'Where to paste the result.
ActiveCell.Offset(0, 22) = ArrayResult
Else
End If
'Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Norie]

Can't you just actually copy/paste?

ArrayResult.Copy ActiveCell.Offset(0,22)

 

[hatstand]

Thank you very much Norie. That has done the trick. Thanks.

 

[hatstand]

As an extra question, if that's ok. How would I transpose the values before being pasted into the sheet?

 

[hatstand]

Hi,

Sorry to be a pest. But, I'm well and truly stuck. I tried everything I know and have done a load of searching. But no answer yet.

The code pastes in no problem, thanks to Norie. But the paste also copies the format and as an addition I would like to transpose the range.

Is this possible, or should I be looking for a different solution.

 

[Norie]

To copy and transpose values you'll need to use PasteSpecial.

Something like this:

ArrayResult.Copy 

ActiveCell.Offset(0,22) xlPasteValue, Transpose:=True

You'll need to check the constant for the first argument, it might need an s, ie Values.

 

[hatstand]

Thanks for the reply. I've replaced the line:

ArrayResult.Copy ActiveCell.Offset(0, 22) with the lines you have written. But, the second line turns red. I'm at the limit of what I know regarding vb, so sorry if I'm being a bit thick.

 

[hatstand]

I'm going to give this one last try before, scrappping it and trying to write another way of doing it. I have tried adding:

With ActiveCell
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

But it gives me a Complie error: Else without if

I would appreciate any help offered. But I guess some things are best scrapped and started again.

 

[Norie]

Well it looks like it wasn't only the 's' you needed to check.

I missed out what is probably the most important part of a paste special.

ArrayResult.Copy 

ActiveCell.Offset(0,22).PasteSpecial Paste:=xlPasteValues, Transpose:=True

By the way, it is just values you want to copy?

You seem to have changed to formats in the code you just posted.

 

[hatstand]

Now that is strange. Just as I had just found the answer (which is exactly the same as yours). Then said hurray, I recived an email with your answer.

Thank you for your time. It's very much appreciated and I've learn't something new.