Paste valuse

[Francis Hookham]

Two questions regarding the following which I am developing

1
Can the long formula be split into short rows using "_" line breaks? It does
not seem to work except where the line break comes after a equals time

3
It would be best if the "Contents" rather than the "Formula" were the value
put into
Sheets("List").Cells(RowNumber, 1).Value

Many thanks

FRancis Hookham

Sub UpdateList()
RowNumber = 2
RefRow = 2
While Sheets("Data").Cells(RefRow, 1).Value <> ""
Sheets("List").Cells(RowNumber, 1).Value = _
"=Data!RC[9]&"" ""&Data!RC[5]&"", ""&Data!RC[10]&
IF(ISBLANK(Data!RC[11]),"""","",
""&Data!RC[11])&IF(ISBLANK(Data!RC[12]),"""","",
""&Data!RC[12])&IF(ISBLANK(Data!RC[13]),"""","",
""&Data!RC[13])&IF(ISBLANK(Data!RC[14]),"""","", ""&Data!RC[14])"
Sheets("List").Cells(RowNumber, 2).Value = _
"=Data!RC[14]&IF(ISTEXT(Data!RC[15]),""
""&Data!RC[15],"""")"
RowNumber = RowNumber + 1
RefRow = RefRow + 1
Wend
End Sub

 

[J.E. McGimpsey]

Two questions regarding the following which I am developing

1
Can the long formula be split into short rows using "_" line breaks? It does
not seem to work except where the line break comes after a equals time

You can break up the formula as long as each "segment" is within
quotes and you join them with the concatenation operator (&). See
below.

3
It would be best if the "Contents" rather than the "Formula" were the value
put into
Sheets("List").Cells(RowNumber, 1).Value

By contents, I take it you mean the results of the formula?


Here's a rewrite of your macro that eliminates the loop and writes
the formulae to Sheet "List" in one step, then replaces the formulae
with their results:

Sub UpdateList2()
Const RefRow As Long = 2
Const RowNumber As Long = 2
Dim rSource As Range
Dim rdest As Range
With Sheets("Data")
Set rSource = .Range(.Cells(RefRow, 1), _
.Cells(RefRow, 1).End(xlDown))
End With
Set rdest = Sheets("List").Cells( _
RowNumber, 1).Resize(rSource.Rows.Count)
With rdest
.FormulaR1C1 = _
"=Data!RC[9]&"" ""&Data!RC[5]&"", ""&Data!RC[10]&" & _
"IF(ISBLANK(Data!RC[11]),"""","", ""&Data!RC[11])&" & _
"IF(ISBLANK(Data!RC[12]),"""","", ""&Data!RC[12])&" & _
"IF(ISBLANK(Data!RC[13]),"""","", ""&Data!RC[13])&" & _
"IF(ISBLANK(Data!RC[14]),"""","", ""&Data!RC[14])"
.Offset(0, 1).FormulaR1C1 = _
"=Data!RC[14]&IF(ISTEXT(Data!RC[15]),"" ""&" & _
"Data!RC[15],"""")"
.Resize(, 2).Value = .Resize(, 2).Value
End With
End Sub

Note: this assumes there will be at least 2 rows of data. If there
may be only one, consider replacing

Set rSource = .Range(.Cells(RefRow, 1), _
.Cells(RefRow, 1).End(xlDown))

with

Set rSource = .Range(.Cells(RefRow, 1), _
.Cells(Rows.Count, 1).End(xlUp))

Sub UpdateList()
RowNumber = 2
RefRow = 2
While Sheets("Data").Cells(RefRow, 1).Value <> ""
Sheets("List").Cells(RowNumber, 1).Value = _
"=Data!RC[9]&"" ""&Data!RC[5]&"", ""&Data!RC[10]&
IF(ISBLANK(Data!RC[11]),"""","",
""&Data!RC[11])&IF(ISBLANK(Data!RC[12]),"""","",
""&Data!RC[12])&IF(ISBLANK(Data!RC[13]),"""","",
""&Data!RC[13])&IF(ISBLANK(Data!RC[14]),"""","", ""&Data!RC[14])"
Sheets("List").Cells(RowNumber, 2).Value = _
"=Data!RC[14]&IF(ISTEXT(Data!RC[15]),""
""&Data!RC[15],"""")"
RowNumber = RowNumber + 1
RefRow = RefRow + 1
Wend
End Sub

 

[Francis Hookham]

Many thanks for your reply a few days ago

I am obviously missing something in my ignorance - trying to combine
worksheet formula experience with VBA lack of experience

Since we are understandably discouraged from attaching files I shall try to
explain

I am trying to take various fields (cells) from each record (row) in
Sheet(³Data²) and concatanate them (with a few ³If²s) into 2 or 3 rows in
Sheets(³List²) -
the first row has initials&surname&address in C1 and phone/email in C2
the second row has ³ ³&FirstName&²: ³&CV in C1 and Interests in C2
If there is a spouse then the third row has ³ ³&SpouseName&²: ³&CV in C1
and Interests in C2
else start again with second record in Sheet(³Data²)

At first (with my posting to you last week) I was trying to concatanate
striaght into Sheets(³List²) and you showed me how to do this but it got
beyond me when there 2 OR 3 rows to deal with in each record (there are
about 80 records)

So I am trying to use variables which get their infromation from
Sheets(²Data²) and then put them into Sheets(³List²) something like this
(Row 1 is a heading row)
Row 2 - L1T1 in R2C1 and L1T2 in R1C2
Row 3 - L2T1 in R3C1 and L2T2 in R3C2
Row 4 - If R#C9 <> ³² then L3T1 and L3T2 else start again with L1T1 in R2C1
and L1T2 in R1C2

This is where I have got to (although I have left the first row as it was =
entering the concatanated formula, with a view later of copying and ³pasting
contents²
The attempt to get L2T1 onwards does not seem to work - all I get from:
L2T1 = " " & Sheets("Data").Cells(DataRow, 8).Value & _
": " & Sheets("Data").Cells(DataRow, 19).Value
is ³ : ³

I am in a mess - can you help?

As many times before - thanks

Francis Hookham

Dim L1T1 As String
Dim L2T1 As String
Dim L3T1 As String
Dim L1T2 As String
Dim L2T2 As String
Dim L3T2 As String
Dim DataRow As Integer
Dim ListRow As Integer
(I wish I understood a bit more about variable and where to declare them! -
trouble is I am not good at taking it in from books - I have to learn slowly
by trial and error and observation which is why this Newsgroup is so
valuable)

Sub UpdateList()
ListRow = 2
DataRow = 2
While Sheets("Data").Cells(DataRow, 1).Value <> ""
L1T1 = Sheets("Data").Cells(DataRow, 10).Value & _
" " & Sheets("Data").Cells(DataRow, 6).Value & _
", " &Sheets("Data").Cells(DataRow, 7).Value & _
if(Sheets("Data").Cells(DataRow, 12).Value =
"","",","&Sheets("Data").Cells(DataRow, 12)
'etc, etc
L1T2 = "=Data!RC16&IF(ISTEXT(Data!RC17),"" ""&Data!RC17,"""")"

L2T1 = " " & Sheets("Data").Cells(DataRow, 8).Value & _
": " & Sheets("Data").Cells(DataRow, 19).Value
L2T2 = "=Data!RC20"

If Sheets("Data").Cells(DataRow, 9).Value <> "" Then
L2T1 = "="" ""&Data!RC9&"": ""&Data!RC21"
L2T2 = "=Data!RC22"
End If

Sheets("List").Cells(ListRow, 1).Value = L1T1
Sheets("List").Cells(ListRow, 2).Value = L1T2
ListRow = ListRow + 1
Sheets("List").Cells(ListRow, 1).Value = L2T1
Sheets("List").Cells(ListRow, 2).Value = L2T2
If L3T1 <> ³² Then
ListRow = ListRow + 1
Sheets("List").Cells(ListRow, 1).Value = L3T1
Sheets("List").Cells(ListRow, 2).Value = L3T2
ListRow = ListRow + 1
End If
DataRow = DataRow + 1
Wend
End Sub

 

[Jim Gordon MVP]

Hi Francis,

May I be a heretic here and suggest a different approach?

Use your Excel workbook as a data source and use Word's data manager to
query the workbook. Put the results into a table and then paste the table
into Excel.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

----------