Tricky Macro Help Please!

Hi

I really need help in writing a macro for the described situation below.

A user will highlight a certain amount of rows (each time will vary) in 1 column

Once the rows in that particular column are highlighted, I would like to copy them to clipboard and then enable them to be pasted back into excel in 1 Cell… This seems really simple, however whenever I try to copy the rows, if I click "in" the particular cell I want to paste it in, excel disables the paste function.

The only way I have been able to do this is in the following order:
Copy select text from excel
Paste in notepad
Copy text from notepad
Click “in” particular cell I would like to paste
Click Paste

This works and is in effect the solution which I would like to automate.

Any help on this would be greatly appreciated.

Below is a link with a screenshot.
http://members.optuszoo.com.au/~joukadjian/untitled1.JPG

Thanks Mark

Hello Mark and welcome to the forum. :)

There's a few ways to do this. I'll suggest two options. Have you done much Excel VBA coding before?

Option 1 - Write a procedure yourself such as:

To get the user to select a 'source' range you could use the inputbox method. Look it up in your VBA helplfile and especially take note of the optional type argument and how it might help you here. You might want to think about how to check that the user has only selected cells within 1 column.
To join the text values within each cell of the range together you could concatenate them using a For Each... Next loop.
To get the user to select a 'target' cell you could use the inputbox method again.


You'll also want to think about various limitations such as maximum viewable / printable characters within a cell etc.... and how to workaround or defensively code for them.


Option 2
As an alternative, you could write a UDF for this. Now, the good news is that Chip Pearson's already done the hard work for you. You can just copy the StringConcat code here:
http://www.cpearson.com/excel/stringconcatenation.aspx

and paste it into a standard code module in your workbook. Note that this will not work if you put the code in a class module.

Then in your worksheet (I'll assume the range F4:F8 contains the text per your attached screenshot) you would put this formula into C6:

=stringconcat(" ",F4:F8)

Read more about how this works by reading the notes on the link.


So there you go. 2 options... the 1st one will challenge you and you will learn something. The 2nd one it's all done for you so it's nice and easy. Take your pick. ;)

Regards,
Colin

Thanks for you responses Colin

I will attempt option 1, but I'm really haven't done much coding at all, so I'm probably facing a very up hill battle.

I will give them both a shot and report back in a few days

Thanks again

Mark

Thanks for you responses Colin

I will attempt option 1, but I'm really haven't done much coding at all, so I'm probably facing a very up hill battle.

I will give them both a shot and report back in a few days

Thanks again

Mark

Hi Mark,

That's excellent. And yes, it will be an uphill battle: but when you reach the top and you can enjoy the view you will find it extremely rewarding!

Don't forget that we're here to help so as you are trying to do this please post questions.

Btw, here's an example from a recent thread on the inputbox method:
http://www.xtremevbtalk.com/showpost.php?p=1281548&postcount=2

It's not quite complete because there will be an error if the user presses the cancel button: something for you to think about.

Colin

Hi

I’m struggling with this one.

Anyway, I’ve used some code that I found in another forum, and I almost have my solution! Well not really, but I’m getting somewhere (I think!)

If you see the code below, it is partially working. It is storing the text value from cell a1 and a2 and storing them under variable a & b and then it concatenates in a list format and places it in sheet 2. Then it goes to cell a2 and a3 and stores it under variable a & b, then goes to sheet 2 etc… all the way until the cells in Sheet 1 column A are empty. The problem is, I don’t want it to store just 2 cells worth, I want it to search the entire column A and concatenate each & every cell that has text in it! I’ve modified the code and worked with it, but I cant seem to do this at all.

Once I get this part, then I can get it to work with the msg box for when the user chooses the range. Ideally that’s my complete solution, but so far no luck.

Any help is much appreciated

Mark

********CODE**********
Sub conc()
For Each cell In Sheets("sheet1").Range("A:A")
If cell.Value <> Empty Then
i = i + 1
a = cell.Value
b = cell.Offset(1, 0).Value
c = a & vbCrLf & b
Sheets("sheet2").Range("A1").Offset(i - 1, 0) = c
End If
Next cell
End Sub

Hi Mark,

That's good progress. :cool:

Here are some pointers to get you going again. Hopefully we can crack this nut and then you can introduce the inputboxes. Btw - when you post code please can you use the code tags to make it easier for us to read? Thanks....

Okay, first up: where are your variable declarations? You should declare all of your variables at the start of your procedure to make it more efficient and to avoid unexpected results. You can (and should) force yourself to declare your variables by putting an Option Explicit Statement at the top of your code module. I can't recommend this practice enough.

Now onto answering your question:
The code in Chip Pearson's StringConcat function gives you a clue:

For Each R In Args(N).Cells
S = S & R.Text & Sep
Next R


For our purposes let's alter it to this:

Option Explicit

Sub Conc()
Dim rngCell As Range, sAllText As String

For Each rngCell In Worksheets(1).Range("A:A")
If Not IsEmpty(rngCell.Value) Then
sAllText = sAllText & rngCell.Value & vbCrLf
End If
Next

MsgBox sAllText

End Sub


Now step through that code and to see how it works. It's pretty crude but it should be a good starting shell given that the final objective is to be looping through cells within a user-defined range.

Colin

Man that's awesome!

Worked brilliantly. I really can't do this stuff on my own... so thanks again.

I'll attempt to get this msgbox incorporated. I'm assuming the msg box prompt for the range should sit outside the loop.. then I somehow need to tell the code that the range is what the user has selected, and then proceed with your code as per above...

good luck to myself! ahah

I'll be back in a few days

Man that's awesome!

Worked brilliantly. I really can't do this stuff on my own... so thanks again.

I'll attempt to get this msgbox incorporated. I'm assuming the msg box prompt for the range should sit outside the loop.. then I somehow need to tell the code that the range is what the user has selected, and then proceed with your code as per above...

good luck to myself! ahah

I'll be back in a few days

You can do this on your own.... is just takes a lot of patience and trial and error to begin with.

For the next step:

I gave you a link to an example in #4.

Note that you're after the InputBox method which is not to be confused with the Inputbox Function or the MsgBox Function. If you look up the InputBox method in the VBA helpfiles there is also an example in there. ;)

Colin

Thanks again for your help... I finally got it! I knew it had to do with the changing the .Range("A:A") to include a variable, but wasn't sure how... anyway, I finally did it, and it works unbelievably! I also got a bit of code to put it to Clipboard instead of just pasting it to another sheet... so my final code was as per below. Thanks again!

Option Explicit

Sub Concatenate_To_Clipboard()
Dim MyData As DataObject
Dim rngCell As Range, sAllText As String
Set rngCell = Application.InputBox(prompt:="Please Select Cells For Copying", Type:=8)
Set MyData = New DataObject
For Each rngCell In Worksheets(1).Range(rngCell.Address)

If Not IsEmpty(rngCell.Value) Then
sAllText = sAllText & rngCell.Value & vbCrLf

End If
Next
' MsgBox sAllText
MyData.SetText sAllText
MyData.PutInClipboard

'Sheets("sheet1").Range("B1") = sAllText
End Sub

Hi Mark,

Well done! You really should be using two range variables though - I've amended your code to allow for that:


Option Explicit

'Requires Reference To Microsoft Forms 2.0 Object Library
Sub Concatenate_To_Clipboard()
Dim MyData As DataObject
Dim rngCell As Range, rngToConcat as Range, sAllText As String

Set rngToConcat = Application.InputBox(prompt:="Please Select Cells For Copying", Type:=8)
Set MyData = New DataObject

For Each rngCell In rngToConcat
If Not IsEmpty(rngCell.Value) Then
sAllText = sAllText & rngCell.Value & vbCrLf
End If
Next

MyData.SetText sAllText
MyData.PutInClipboard
End Sub



There's a few bits that could be tidied up but I'll just give you one more code aspect to think about for now....

Set rngToConcat = Application.InputBox(prompt:="Please Select Cells For Copying", Type:=8)

What happens if the user presses cancel instead of selecting a range? We don't like runtime errors in final code versions! ;)


I haven't tried running your code but I have a question (suspicion). If you put it on the clipboard like this, once the procedure has finished and you select a cell and press CTRL + V to paste, does it actually paste the data in the clipboard into one cell (which I think is the aim of the project) or into cells down the column? If it's the latter then isn't all of this exactly the same as a normal copy and paste?

Colin

Colin

Thanks for the tips. yes, I really don't want to have Run Time errors, I was looking for some code to solve this, but didn't get very far.

In terms of the final solution, you're right in saying that it will just paste it as it's copied in many cells, however, it does enable the user to specifically click into a particular cell (so that the cursor is blinking) and then press ctrl V and it will paste it all in that cell as a list. Without the code, excel would not have previously allowed that - it disables the paste command.

I will re run the code, but yes, I would like some sort of error handling... i.e. "are you sure you want to cancel?" or soemthing along those lines. I will keep trying

Thanks again.

I really don't want to have Run Time errors, I was looking for some code to solve this, but didn't get very far.
Well you have 2 options.

Option 1 would be to declare your rngToConcat as a variant and to check the value it holds on the following line.

Option 2 would be to use an On Error Resume Next statement like this:


'Requires Reference To Microsoft Forms 2.0 Object Library
Sub Concatenate_To_Clipboard()
Dim MyData As DataObject
Dim rngCell As Range, rngToConcat As Range, sAllText As String

On Error Resume Next
Set rngToConcat = Application.InputBox(prompt:="Please Select Cells For Copying", Type:=8)

On Error GoTo 0
If rngToConcat Is Nothing Then
MsgBox "User pressed cancel"
Else
For Each rngCell In rngToConcat
If Not IsEmpty(rngCell.Value) Then
sAllText = sAllText & rngCell.Value & vbCrLf
End If
Next

If Len(sAllText) > 0 Then
Set MyData = New DataObject
With MyData
.SetText sAllText
.PutInClipboard
End With
End If
End If

Set MyData = Nothing
Set rngToConcat = Nothing
End Sub


Colin

Colin

Thanks so much for your help.

Excel Guru! Works perfectly well.

Mark