Macro Question formula

[Juan Carlos]

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Dave Peterson]

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Juan Carlos]

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Dave Peterson]

Maybe you could either explain it better or experiment manually.

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Juan Carlos]

Cell A1=123 (Sheet 5)
A2=456 (Sheet 5)
A3=789 (Sheet 5)
My formula is in A4(Sheet 5) =A1&A2&A3

RESULT IS A4=123456789

The numbers of A1 come from sheet 1
The numbers of A2 come from sheet 2
The numbers of A3 come from sheet 3
The result A4 is in sheet 5

The number in A1 can come from either sheet 1 or sheet 4

What I need is a formula that gives me a different result if the numbers in
A1 came from Sheet 4 instead of Sheet 1.

The formula will be =A2&A3 and the result will be 456789. and the only
condition if where the data is coming from.

I hope this is clear enough.

Thanks

i hope this is clear enoung.

thanks

Maybe you could either explain it better or experiment manually.

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

Juan Carlos wrote:

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Dave Peterson]

Maybe you can use the same rule that A1 uses to determine where to get the
values.

But you haven't shared that.

Cell A1=123 (Sheet 5)
A2=456 (Sheet 5)
A3=789 (Sheet 5)
My formula is in A4(Sheet 5) =A1&A2&A3

RESULT IS A4=123456789

The numbers of A1 come from sheet 1
The numbers of A2 come from sheet 2
The numbers of A3 come from sheet 3
The result A4 is in sheet 5

The number in A1 can come from either sheet 1 or sheet 4

What I need is a formula that gives me a different result if the numbers in
A1 came from Sheet 4 instead of Sheet 1.

The formula will be =A2&A3 and the result will be 456789. and the only
condition if where the data is coming from.

I hope this is clear enough.

Thanks

i hope this is clear enoung.

thanks

Maybe you could either explain it better or experiment manually.

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

:

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

Juan Carlos wrote:

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Juan Carlos]

This is the rule A1 use

Sheets("Sheet1").Range("A56:A" & Trim(Str(300 + iRows))).Copy
Sheets("Sheet3").Range("A1:A" & Trim(Str(3 + iRows))).PasteSpecial
Paste:=xlPasteValues

With Worksheets("Sheet3")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Worksheets("Sheet2").Range("d5:d" & 75 + iRows).Copy
DestCell.PasteSpecial Paste:=xlPasteValues

And is the same for Columns B and C


Data goes like this

(Sheet 3) Colum A1 to A20 comes from Sheet 1 and Sheet 2
(Sheet 3) Colum B1 to B20 comes from Sheet 1 and sheet 2
(Sheet 3) Colum C1 to C20 comes from Sheet 1 and Sheet 2

The data that go in column A( sheet 3) is copy and paste from sheet 1 and
then the data that come from sheet 2 is copy and paste to the last empty row
in A (sheet 3). That is the only condition I’m using.

So my formula will take data that came form A1, B1 and C1 and put everything
is D1 formula = A1&B1&C1 when the data of A1 if from Sheet 1


But If the data in A1 came from Sheet 2 I need to do Formula =B1&C2

i'dont even know if that is possible

Maybe you can use the same rule that A1 uses to determine where to get the
values.

But you haven't shared that.

Cell A1=123 (Sheet 5)
A2=456 (Sheet 5)
A3=789 (Sheet 5)
My formula is in A4(Sheet 5) =A1&A2&A3

RESULT IS A4=123456789

The numbers of A1 come from sheet 1
The numbers of A2 come from sheet 2
The numbers of A3 come from sheet 3
The result A4 is in sheet 5

The number in A1 can come from either sheet 1 or sheet 4

What I need is a formula that gives me a different result if the numbers in
A1 came from Sheet 4 instead of Sheet 1.

The formula will be =A2&A3 and the result will be 456789. and the only
condition if where the data is coming from.

I hope this is clear enough.

Thanks

i hope this is clear enoung.

thanks

Maybe you could either explain it better or experiment manually.

Juan Carlos wrote:

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

:

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

Juan Carlos wrote:

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Dave Peterson]

So the first portion (A1:A(3+irows) comes from Sheet3.
The portion under that comes from Sheet2.

I'm not sure how Upload or Sport fit into the rules or the formula.

But maybe this will help (or not):

dim RngToCopy as range
dim DestCell as range

with worksheets("sheet1")
'drop the trim() and str() stuff, it's just clutter.
set rngtocopy = .range("a56:a" & 300+irow)
end with
with worksheets("sheet3")
set destcell = .range("a1") 'let excel figure out how big it should be.
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

Now you can use that rngtocopy to determine how many rows/columns should be
looked at:

with worksheets("Upload")
with .Range("AS2").resize(rngtocopy.rows.count,1) 'single column
.formular1c1 = "something retrieved from Sheet1????"
end with
end with

All untested, uncompiled.

This is the rule A1 use

Sheets("Sheet1").Range("A56:A" & Trim(Str(300 + iRows))).Copy
Sheets("Sheet3").Range("A1:A" & Trim(Str(3 + iRows))).PasteSpecial
Paste:=xlPasteValues

With Worksheets("Sheet3")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Worksheets("Sheet2").Range("d5:d" & 75 + iRows).Copy
DestCell.PasteSpecial Paste:=xlPasteValues

And is the same for Columns B and C

Data goes like this

(Sheet 3) Colum A1 to A20 comes from Sheet 1 and Sheet 2
(Sheet 3) Colum B1 to B20 comes from Sheet 1 and sheet 2
(Sheet 3) Colum C1 to C20 comes from Sheet 1 and Sheet 2

The data that go in column A( sheet 3) is copy and paste from sheet 1 and
then the data that come from sheet 2 is copy and paste to the last empty row
in A (sheet 3). That is the only condition I’m using.

So my formula will take data that came form A1, B1 and C1 and put everything
is D1 formula = A1&B1&C1 when the data of A1 if from Sheet 1

But If the data in A1 came from Sheet 2 I need to do Formula =B1&C2

i'dont even know if that is possible

Maybe you can use the same rule that A1 uses to determine where to get the
values.

But you haven't shared that.

Cell A1=123 (Sheet 5)
A2=456 (Sheet 5)
A3=789 (Sheet 5)
My formula is in A4(Sheet 5) =A1&A2&A3

RESULT IS A4=123456789

The numbers of A1 come from sheet 1
The numbers of A2 come from sheet 2
The numbers of A3 come from sheet 3
The result A4 is in sheet 5

The number in A1 can come from either sheet 1 or sheet 4

What I need is a formula that gives me a different result if the numbers in
A1 came from Sheet 4 instead of Sheet 1.

The formula will be =A2&A3 and the result will be 456789. and the only
condition if where the data is coming from.

I hope this is clear enough.

Thanks

i hope this is clear enoung.

thanks

:

Maybe you could either explain it better or experiment manually.

Juan Carlos wrote:

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

:

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

Juan Carlos wrote:

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select

 

[Juan Carlos]

Dave,
Thanks for the help.
Juan

So the first portion (A1:A(3+irows) comes from Sheet3.
The portion under that comes from Sheet2.

I'm not sure how Upload or Sport fit into the rules or the formula.

But maybe this will help (or not):

dim RngToCopy as range
dim DestCell as range

with worksheets("sheet1")
'drop the trim() and str() stuff, it's just clutter.
set rngtocopy = .range("a56:a" & 300+irow)
end with
with worksheets("sheet3")
set destcell = .range("a1") 'let excel figure out how big it should be.
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

Now you can use that rngtocopy to determine how many rows/columns should be
looked at:

with worksheets("Upload")
with .Range("AS2").resize(rngtocopy.rows.count,1) 'single column
.formular1c1 = "something retrieved from Sheet1????"
end with
end with

All untested, uncompiled.

This is the rule A1 use

Sheets("Sheet1").Range("A56:A" & Trim(Str(300 + iRows))).Copy
Sheets("Sheet3").Range("A1:A" & Trim(Str(3 + iRows))).PasteSpecial
Paste:=xlPasteValues

With Worksheets("Sheet3")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Worksheets("Sheet2").Range("d5:d" & 75 + iRows).Copy
DestCell.PasteSpecial Paste:=xlPasteValues

And is the same for Columns B and C

Data goes like this

(Sheet 3) Colum A1 to A20 comes from Sheet 1 and Sheet 2
(Sheet 3) Colum B1 to B20 comes from Sheet 1 and sheet 2
(Sheet 3) Colum C1 to C20 comes from Sheet 1 and Sheet 2

The data that go in column A( sheet 3) is copy and paste from sheet 1 and
then the data that come from sheet 2 is copy and paste to the last empty row
in A (sheet 3). That is the only condition I’m using.

So my formula will take data that came form A1, B1 and C1 and put everything
is D1 formula = A1&B1&C1 when the data of A1 if from Sheet 1

But If the data in A1 came from Sheet 2 I need to do Formula =B1&C2

i'dont even know if that is possible

Maybe you can use the same rule that A1 uses to determine where to get the
values.

But you haven't shared that.

Juan Carlos wrote:

Cell A1=123 (Sheet 5)
A2=456 (Sheet 5)
A3=789 (Sheet 5)
My formula is in A4(Sheet 5) =A1&A2&A3

RESULT IS A4=123456789

The numbers of A1 come from sheet 1
The numbers of A2 come from sheet 2
The numbers of A3 come from sheet 3
The result A4 is in sheet 5

The number in A1 can come from either sheet 1 or sheet 4

What I need is a formula that gives me a different result if the numbers in
A1 came from Sheet 4 instead of Sheet 1.

The formula will be =A2&A3 and the result will be 456789. and the only
condition if where the data is coming from.

I hope this is clear enough.

Thanks

i hope this is clear enoung.

thanks

:

Maybe you could either explain it better or experiment manually.

Juan Carlos wrote:

Dave,
Thank for the feedback, but I don’t know what formula to use to make this
work.

:

I would get the formula working manually.

After that's done, turn on the macro recorder.
Record a macro when you select the cell with the formula, hit F2, then enter.
Then stop recording.

Then take a look at that code for the formula.

Juan Carlos wrote:

I created this macro so it will collect info from 3 cells a put a phrase in
AS2. The data that is on these 3 cells comes form other sheets. (Sheet 1, 2,
and 3)
What I need this macro to do also is to collect info from 2 cells instead
of 3. If the information in cell is RC[-20] is coming from sheet 4.
Any suggestions?

Sheets("Upload").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",RC[-43]&'Sport'!R6C29&'Upload'!RC[-20])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS500"), Type:=xlFillDefault
Range("AS2:AS500").Select