|
Macro Question 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 |
Macro Question formula
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 |
Macro Question formula
Dave,
Thank for the feedback, but I dont know what formula to use to make this work. "Dave Peterson" wrote: 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 |
Macro Question formula
Maybe you could either explain it better or experiment manually.
Juan Carlos wrote: Dave, Thank for the feedback, but I dont know what formula to use to make this work. "Dave Peterson" wrote: 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 -- Dave Peterson |
Macro Question formula
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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson |
Macro Question formula
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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Macro Question formula
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 Im 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 "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Macro Question formula
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. Juan Carlos wrote: 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 Im 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 "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Macro Question formula
Dave,
Thanks for the help. Juan "Dave Peterson" wrote: 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. Juan Carlos wrote: 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 "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 05:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com