Re: Multiple Regression Macro with Specific Output
I think that is the answer. Very flexible way to run the regressions.
Thank you for the help on this.
Re: Multiple Regression Macro with Specific Output
I think that is the answer. Very flexible way to run the regressions.
Thank you for the help on this.
Hello,
I am hoping someone knows how to perform a multiple regression in VBA. I think it would be easiest to name the ranges for the Dependent (Y) and Independent Variables (Xs), but I do not know the syntax for running multiple regression, and the macro recorder does not provide much insight on this.
I have attached a worksheet that shows the regression that I attempting to run. I would like to output the results in the labeled cell.
Thank you for any help on this one. forum.ozgrid.com/index.php?attachment/50692/
Re: Loop with Formula
That worked perfectly. How can I learn to crank VBA code like that?
Re: Looping Through all Rows and Entering a Formula to Calculate Change
I want to calculate the change over time for the estimates that reside in every row, and the part I am stuck on is that the rows are not all the same length. The cell on the far right in every row is the ending period estimate, and I want to divide that cell by the cell that is at the far left of every row.
Does this make sense?
Hello,
I want to create a loop that goes down all the rows in my spreadsheet and does the formula (end/beginning -1) for all the rows of cells. The "end" cell is the cell that is the farthest right in the row (some sort of end.xlright) and the "beginning" cell is column D of the row that is being calculated.
I have attached the workbook to give a better idea of what it is I am trying to accomplish.
I appreciate any help on this.
Hello,
I want to create a loop that goes down all the rows in my spreadsheet and does the formula (end/beginning -1) for all the rows of cells. The "end" cell is the cell that is the farthest right in the row (some sort of end.xlright) and the "beginning" cell is column D of the row that is being calculated.
I have attached the workbook to give a better idea of what it is I am trying to accomplish.
I appreciate any help on this.
Re: Run Time Error 1004 while dimesioning and setting variables
On second try, I'm not sure if that was the issue. If someone could look through and let me know if they see anything fishy, that would be great.
Thanks
Re: Run Time Error 1004 while dimesioning and setting variables
Figured it out. The string was simply too long.
Hello,
I received a 1004 error with the following code. I can not seem to figure out what could be causing this error. Can anyone see something obvious that is wrong with the way I have set my variable?
Sub Edit_Individual_Ticker()
Dim myrange1 As Range
Dim myrange2 As Range
Dim myrange3 As Range
Dim myrange4 As Range
Dim myrange5 As Range
Dim myrange6 As Range
Dim myrange7 As Range
Dim myrange8 As Range
Dim myrange9 As Range
Dim myrange10 As Range
Dim myrange11 As Range
Dim myrange12 As Range
Dim myrange13 As Range
Dim myrange14 As Range
Dim myrange15 As Range
Dim myrange16 As Range
Set myrange1 = Range("g5:g48,g50:g82,g84:g101,g103:g129,g131:g141,g143:g177,g179:g187,g189:g241,g243:g264,g266:g278,g291:g347,g349:g366,g368:g426")
Set myrange2 = Range("M5:M48,M50:M82,M84:M101,M103:M129,M131:M141,M143:M177,M179:M187,M189:M241,M243:M264,M266:M278,M291:M347,M349:M366,M368:M426")
Set myrange3 = Range("S5:S48,S50:S82,S84:S101,S103:S129,S131:S141,S143:S177,S179:S187,S189:S241,S243:S264,S266:S278,S291:S347,S349:S366,S368:S426")
Set myrange4 = Range("g5:g48,g50:g82,g84:g101,g103:g129,g131:g141,g143:g164,g166:g175,g177:g233,g235:g252,g254:g312,g314:g325,g327:g348,g350:g363,g365:g372,g374:g378,g380:g392,g394:g395,g397:g420,g422:430,g432:g434,g436:g448,g450:g454")
Set myrange5 = Range("M5:M48,M50:M82,M84:M101,M103:M129,M131:M141,M143:M164,M166:M175,M177:M233,M235:M252,M254:M312,M314:M325,M327:M348,M350:M363" & _
"M365:M372,M374:M378,M380:M392,M394:M395,M397:M420,M422:430,M432:M434,M436:M448,M450:M454")
Set myrange6 = Range("S5:S48,S50:S82,S84:S101,S103:S129,S131:S141,S143:S164,S166:S175,S177:S233,S235:S252,S254:S312,S314:S325,S327:S348,S350:S363" & _
"S365:S372,S374:S378,S380:S392,S394:S395,S397:S420,S422:430,S432:S434,S436:S448,S450:S454")
Set myrange7 = Range("g5:g37,g39:g73,g75:g83,g85:g137,g139:g151")
Set myrange8 = Range("M5:M37,M39:M73,M75:M83,M85:M137,M139:M151")
Set myrange9 = Range("S5:S37,S39:S73,S75:S83,S85:S137,S139:S151")
Set myrange10 = Range("g5:g48,g50:g82,g84:g101,103:g129,g131:g141,g143:g177,g179:g187,g189:g241,g243:g264,g266:g278,g280:g289,g291:g347," & _
"g349:g366,g368:g426,g428:g441,g443:g450,g452:g456,g458:g470,g472:g473,g475:g498,g500:g508,g510:g512,g514:g525,g527:g532")
Set myrange11 = Range("M5:M48,M50:M82,M84:M101,103:M129,M131:M141,M143:M177,M179:M187,M189:M241,M243:M264,M266:M278,M280:M289,M291:M347," & _
"M349:M366,M368:M426,M428:M441,M443:M450,M452:M456,M458:M470,M472:M473,M475:M498,M500:M508,M510:M512,M514:M525,M527:M532")
Set myrange12 = Range("S5:S48,S50:S82,S84:S101,103:S129,S131:S141,S143:S177,S179:S187,S189:S241,S243:S264,S266:S278,S280:S289,S291:S347," & _
"S349:S366,S368:S426,S428:S441,S443:S450,S452:S456,S458:S470,S472:S473,S475:S498,S500:S508,S510:S512,S514:S525,S527:S532")
Set myrange13 = Range("g5:g18,g20:g27,g29:g33,g35:g47,g49:g50,g52:g75,g77:g85,g87:g89,g91:g102,g104:g109")
Set myrange14 = Range("M5:M18,M20:M27,M29:M33,M35:M47,M49:M50,M52:M75,M77:M85,M87:M89,M91:M102,M104:M109")
Set myrange15 = Range("S5:S18,S20:S27,S29:S33,S35:S47,S49:S50,S52:S75,S77:S85,S87:S89,S91:S102,S104:S109")
Set myrange16 = Range("Y5:Y18,Y20:Y27,Y29:Y33,Y35:Y47,Y49:Y50,Y52:Y75,Y77:Y85,Y87:Y89,Y91:Y102,Y104:Y109")
Display More
Re: Is there a reason that this macro does not jump to the next worksheet?
Hello,
I had it that way before, and I am sure the macro is not stepping to the next worksheet. I was stepping through the macro one line at a time and was running into the same problem.
I figured out a workaround by having the edit_data code go to the next worksheet at the end of the code sequence instead of relying on the loop to step through the next worksheet.
Thank you.
Hello,
I have two macros. One of the macro is designed to loop through all the worksheets in a file and perform the same operations on them. For some reason, this macro is not jumping to the next worksheet and instead is looping through the same worksheet the whole time.
Here are both of the macros I am using. THe second macro is ugly, but it gets the job done.
Sub Edit_Data_Loop()
Application.ScreenUpdating = False
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Application.Run ("Edit_Data")
Next wks
End Sub
Sub Edit_data()
Application.ScreenUpdating = False
Range("B39").Select
ActiveCell.Range("a1:b1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C39").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("F39").Select
ActiveSheet.Paste
Range("C39").Select
ActiveCell.Formula = "=MONTH(B39)"
Range("D39").Select
ActiveCell.Formula = "=YEAR(b39)"
Range("E39").Select
ActiveCell.Formula = "=c39&"" ""&d39"
Range("C39:E39").Copy
Range("b39").Select
Selection.End(xlDown).Select
ActiveCell.Range("B1:D1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveSheet.Calculate
Range("c39:d39").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("c39").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="1", Replacement:="January", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="2", Replacement:="February", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="3", Replacement:="March", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="4", Replacement:="April", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="5", Replacement:="May", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="6", Replacement:="June", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="7", Replacement:="July", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="8", Replacement:="August", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="9", Replacement:="September", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="10", Replacement:="October", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="11", Replacement:="November", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="12", Replacement:="December", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("g39").Select
ActiveCell.Formula = "=IF(ISERROR(F39/F40-1),0,(F39/F40-1))"
Selection.Copy
Range("f39").Select
Selection.End(xlDown).Select
ActiveCell.Range("b1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("g38").Value = "Return"
ActiveSheet.Calculate
End Sub
Display More
Re: Pasting Template File To All Other Worksheets
Never mind I figured out a better way with a different post.
Hello,
I would like to copy a range of cells from a template worksheet and paste them onto all other worksheets in the file. I have some code that is currently not working. I can copy the cells I want, but I cannot seem to figure out the best way to paste them into the other worksheets. Here is what I am currently working with:
Sub copy_template()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Sheets("template").Range("a1:t400").copy
wks.Range("a1").Select
ActiveCell.Paste
Next wks
End Sub
Any help on this topic is greatly appreciated.
Hello,
Is there a formula I can use that will make the value in a cell be the same as the name of a worksheet?
Thank you for the help.
I am trying to make the columns in the attached chart not sit on top of each other. I have tried the method of putting the zero columns in between, but that does not look very good. I was hoping someone could help me edit this chart to make it more presentatble. forum.ozgrid.com/index.php?attachment/47056/
Re: Loop through rows and auto fill
I just tried it, and that did in fact work. Thanks again.
Re: Loop through rows and auto fill
If I do not want to paste the contents of the cells in column B (Bloomb code) as a value, can I simply remove the .value = .value statement from the code?
forum.ozgrid.com/index.php?attachment/47012/I need to find out the coding for a loop that will select the cells in columns (A:C) and perform an autofill. I need the loop to skip the next 42 rows after performing the autofill and do it again for the next set of three cells in columns (A:C). I have attached the worksheet I am using to better explain what it is that I am trying to accomplish.
I would like to loop through column B in the first sheet of the worksheet I have attached and place a formula in column C directly to the right of the value in column B.
I am guessing this is not too complicated, but I am not too familiar with the syntax involved with making a loop. forum.ozgrid.com/index.php?attachment/46889/