Assigning Formula

B

Brutium

Board Regular
Joined
Mar 27, 2009
Messages
188
Hello Everyone,

I have a rather peculiar situation. I surely hope someone will help me....

Using a macro, I created a number of sheets using a template. What I now need to do is figure out is how to assign a formula to different cells (the data comes from the various sheets that I created). I guess I need to replicate the same formula, but I can't figure out how to do it.

Any help would be greatly appreciated.

Thank you
Brutium
 
Sort by date Sort by votes

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
It seems to be coded as it should...what happens if another variable (n) is added?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Addsheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, MasterSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rngTopOfList <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    n = 0<br><br><SPAN style="color:#00007F">With</SPAN> Sheets("tools")<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngTopOfList = .Range("B3")<br>    <br>    LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row<br>    <br>    MasterSheet = ActiveWorkbook.Sheets.Count<br>    <br>    <SPAN style="color:#00007F">If</SPAN> LR > rngTopOfList.Row <SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR - rngTopOfList.Row<br>            ActiveWorkbook.Sheets(MasterSheet).Copy _<br>            after:=Worksheets(MasterSheet + i - 1)<br>            ActiveWorkbook.Sheets(MasterSheet + i).Name = _<br>            rngTopOfList.Offset(i).Value <SPAN style="color:#007F00">'''Can be replaced with wsName if the following line is moved before.</SPAN><br>            <br>            <SPAN style="color:#007F00">''' Here begins changes</SPAN><br>            wsName = rngTopOfList.Offset(i).Value<br>            <br>            ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Value = _<br>                    "=" & wsName & "!M9"<br>            n = n + 1<br>            <SPAN style="color:#007F00">'''</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> rngTopOfList = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Good night Repairman615

I hope to talk to you tomorrow.

Thanks again for all your help...

Brutium
 
Upvote 0
Hello Brutium,

Maybe these message boxes will point to the glitch.


Try the following to attemp a troubleshoot:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Addsheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, MasterSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rngTopOfList <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    n = 0<br><br><SPAN style="color:#00007F">With</SPAN> Sheets("tools")<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngTopOfList = .Range("B3")<br>    <br>    LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row<br>    <br>    MasterSheet = ActiveWorkbook.Sheets.Count<br>    <br>    <SPAN style="color:#00007F">If</SPAN> LR > rngTopOfList.Row <SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR - rngTopOfList.Row<br>            ActiveWorkbook.Sheets(MasterSheet).Copy _<br>            after:=Worksheets(MasterSheet + i - 1)<br>            ActiveWorkbook.Sheets(MasterSheet + i).Name = _<br>            rngTopOfList.Offset(i).Value <SPAN style="color:#007F00">'''Can be replaced with wsName if the following line is moved before.</SPAN><br>            <br>            <SPAN style="color:#007F00">''' Here begins changes</SPAN><br>            wsName = rngTopOfList.Offset(i).Value<br>            <br>            ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Value = _<br>                    "=" & wsName & "!M9"<br><br>MsgBox ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Address<br>MsgBox ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Value<br>            <br>            n = n + 1<br>            <SPAN style="color:#007F00">'''</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> rngTopOfList = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>
 
Upvote 0
Good morning repairman615,

It is still the same. I was wondering...
If my "Worksheet" is renamed after the creation of the additional sheets, could it be the cause of the fact that the newly created sheets do not align properly in my summary sheet?

Also, is there a way I can send you a picture of what is happening? I tried to click on the icon to Insert an Image, but I can't do it. It is asking for an "http://" address and even if I give it the path where I placed the image it does not see it.

Once again...
Thank you for all your efforts.
Brutium
 
Upvote 0
Hello Repairman615,
:):) I resolved the problem!! :):)

All it was, was the starting point from where I started reading my list in Tools.
Now it works OK but (and here is another issue) my summary does not populate vertically.
If you have any suggestions they would be greatly appreciated.

Brutium
 
Upvote 0
Hello repairman615,

I made a few changes to the code and I solved this problem. Here is the complete code:
Sub Addsheets()
Dim LR As Long, i As Long, MasterSheet As Long
Dim rngTopOfList As Range
Dim wsName As String
Dim n As Integer

n = 0
With Sheets("tools")

Set rngTopOfList = .Range("B3")

LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row

MasterSheet = ActiveWorkbook.Sheets.Count

If LR > rngTopOfList.Row Then

For i = 0 To LR - rngTopOfList.Row
ActiveWorkbook.Sheets(MasterSheet).Copy _
after:=Worksheets(MasterSheet + i - 1)
ActiveWorkbook.Sheets(MasterSheet + i).Name = _
rngTopOfList.Offset(i).Value '''Can be replaced with wsName if the following line is moved before.

''' Here begins changes
wsName = rngTopOfList.Offset(i).Value

ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Value = _
"=" & wsName & "!M9"
n = n + 1
'''
Next i

End If

ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value
Worksheets("MasterSheet").Visible = xlSheetVeryHidden
Worksheets("Tools").Visible = xlSheetVeryHidden
End With
Set rngTopOfList = Nothing
End Sub
Click to expand...

Now my issue is that the code does not replicate down the list. That is I need to get the same info coming from the newly created sheets, to populate C10:C52 for the first sheet then D10:D52 for the second sheet, E10:E52 ....L10:L52 for the last sheet. So far, I only get C9:L9 coming from all the sheets.

Brutium
 
Upvote 0
Glad to hear about the progress!

I understand what you are trying to do. This could work with a loop within the loop. I will have a look tonight although I may not have enough time (very busy (and tired)).

Something like:

Code: 
Dim J as interger
Dim x as interger
 
'"loop starts"
x=0
 
for j = 1 to 42'''what ever it takes to go from row 9 to row 52
ActiveWorkbook.Sheets("Class").Range("C9").Offset(x, n).Value = _
"=" & wsName & "!M9"
 
x=x+1
next j

In other words, while in the loop, use an additional loop to place the formula 42 or so times utilizing the offset().

Does this make sense?

The formula will need some kind of adjustment also to change the reference cell too. Hopefully the next cell you would like is cell M10. ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
If/Else conditions assigned to an image to run various actions
Replies
4
Views
93
SanjayGMusafir
SanjayGMusafir
J
  • Question
Dependent dropdown with offset formula
Replies
5
Views
60
juliecooper255
J
L
  • Question
Random employee assignment to accounts that stops assigning Employees when the count reaches 25
Replies
7
Views
63
Lisa116
L
Knockoutpie
  • Question
Assigning Values based on a key value
Replies
4
Views
171
Joe4
Joe4
Yecart77
  • Question
Formula working on first row but not subsequent rows
Replies
1
Views
90
awoohaw
awoohaw

Forum statistics

Threads
1,215,086
Messages
6,123,043
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top