Last week at the Chandoo.org Forums, MarnieB asked:
“I have been asked to produce a chart that looks like the spokes in a wheel. Lines for each data point that start from the same point in the middle and go out in different directions. The length of the line indicates the value of the data point. There are only 5 or 6 data points.”
Lets see how we can produce such a “Spoke Chart” in Excel.
Disclaimer: Before I go too far I want to say that this post isn’t recommending the use of this type of chart. The post is about introducing techniques which you can use as the basis of many custom chart types. The post just uses this chart as a simple example.
EXCEL CHART TYPES
Excel doesn’t have a native Spoke Chart in its catalog of built in Chart Types.
As MarbnieB found out, Radar Chart give some level of simulation, but there not ideal for what MarnieB’s boss wanted.
Luckily for us Excel has a Scatter Chart and this chart type can be used as a veritable drawing board for your own purposes.
The Scatter Chart draws lines between sets of coordinates in the X-Y plane.
Typically Scatter Chart are used for Plotting two variables against each other where neither the X or Y axis has a regular occurrence frequency,
But Scatter Charts can also be used for adding custom chart types as we will see below.
MARNIEB’s SPOKE CHART
Lets look at MarnieB’s specifications:
- It should have 5 or 6 spokes
- Spokes radiate out from a central hub
- The length of the spokes should reflect the spokes value
So it will look something like this:
(Not drawn to scale)
We can imagine that the center of the Spoke is at a position X=0, Y=0 or (0, 0) on the Cartesian plane.
We can then break up a circle into a number of segments n. MarnieB’s requirements n = 6.
As a full circle is 360 degrees we can see that each spoke will be separated by 360/6 = 60 Deg
Hence there will be spokes at:
- 0 Deg
- 60 Deg
- 120 Deg
- 180 Deg
- 240 Deg
- 300 Deg
The length of each spoke will be supplied by MarnieB.
We can use the Scatter chart to plot each Spoke as a separate series on the scatter chart.
Each series will consist of two points, being the center point (0, 0) and another point at the end of the spoke (x, y).
We will need to determine the X and Y values for each end of the spoke.
Using some simple trigonometry we see that:
X = Length * Cos ( angle )
Y = Length * Sin ( angle )
Now we know the angles and lengths and so in Excel we can setup a small table to calculate the X, Y values for each end of the spokes.
Using Excel we need to remember that Excel requires angles in radians. This just requires a simple modification to the formula to:
X = Length * Cos (Radians( angle ) )
Y = Length * Sin (Radians( angle ) )
Setup the Chart Series
Before we jump in you can follow along this example using a new Excel file or the worked Example File, Excel 97/03, Excel 07/10.
As mentioned above each spoke will require two points
Point 1, The center of the spoke at 0,0 and a point at X, Y
In Excel we setup a small table of the Inputs including the Point Id, Angle and Length
We can then add some formulas to take the inputs and convert them to X, Y Cartesian coordinates using the formulas described above.
Putting the Chart Together
Once you have the Spoke coordinates you can construct the chart
With NO data selected, goto the Insert Ribbon and select Scatter, Scatter with Straight Lines
A blank chart will appear on the Screen
You can resize and shift the chart to a useable location if you require.
Right Click on the Chart and select the Select Data option
The following dialog appears:
Select the Add, button and the Edit Series dialog appears.
The Series Name: is linked to the Spokes Name $A$4
The Series X values: is linked to the two Chart X values: E3:E4
The Series Y values: is linked to the two Chart X values: F3:F4
Ok when complete
You can now go ahead and add the other 5 Series to the chart by selecting the Add button.
Your Select data dialog will now appear like:
And the chart will appear something like:
Cleanup and Format the Chart
We now need to clean up and format the chart
Select and Delete the Charts Title, Chart Legend & Horizontal Grid Lines
Select Each Axis in Turn, Right Click and Format Axis
Set the Minimum and Maximum values to something greater than our data eg: -20, +20 in our example. The Minimum and Maximum for the Horizontal and Vertical axis bust be the same so that the chart scales correctly.
Resize the Chart so that it is approximately square
Leave the axis for now, it is simple to delete them later
Select each spoke in turn
Right Click and select Format Data Series
Set the Marker Options, Marker Fill, Line Style, Line Color to suit your preferences
If you want to add a marker to one end of the line, Select the line, then use the Right/Left arrow keys to select the end you want, Ctrl 1 to Edit the Format of that end only.
Your chart should now be something like:
Add Data Labels
Select each spoke in turn using the Up/Down arrows, then using the Right/Left Arrow keys, select the outer end of the Spoke
Right Click and Add Data Label
A Default value will appear which is the Y Value for the data point
Right Click on the Data Label then select Format Data Label or simply press Ctrl 1
Unclick the Y Value and Tick the Series Name
Repeat for each Spoke.
You may want to change the alignment for some of the Data Labels so they don’t clash with the spokes.
Add Circular Grid Lines
Lets add 3 Grid lines at a Maximum value and at 1/3rd and 2/3rds of that value
First we need to calculate the Grid Values
In cells C22:C24 I added 3 formulas
I have then assigned 3 Named Formulas to the 3 cells
Max_Circle: =$C$22
Mid_Circle: =$C$23
Min_Circle: =$C$24
To make a circle on a Scatter Chart we will need some points for the X and Y values for each point around the circle
To achieve this I will use a few Named Formulas:
t: =RADIANS(ROW(OFFSET(‘1’!$A$1,,,361,1)))
X_1: =SIN(t)*Max_Circle
Y_1: =COS(t)*Max_Circle
X_2: =SIN(t)*Mid_Circle
Y_2: =COS(t)*Mid_Circle
X_3: =SIN(t)*Min_Circle
Y_3: =COS(t)*Min_Circle
Lets look at a few of these and see what is going on:
t: =RADIANS(ROW(OFFSET(‘1’!$A$1,,,361,1)))
This formula sets up an Array of 360 values from 1 to 361, corresponding to 1 degree to 361 degrees. This occurs using the formula: = ROW(OFFSET(‘1’!$A$1,,,361,1)) which takes the Row value of an temporary range which is setup from cell A1 and offset 0 Rows, 0 Columns and is 361 rows high and 1 Column wide.
In a blank cell C27 type: = ROW(OFFSET(‘1’!$A$1,,,361,1)) press F9 not Enter
Excel will display ={1;2;3;4;5; … ;355;356;357;358;359;360;361}
1 number for each row, which will be used to represent the degrees of the circle
In a blank cell C28 type: =Radians( ROW(OFFSET(‘1’!$A$1,,,361,1))) press F9 not Enter
Excel will display ={0.0174532925199433;0.0349065850398866;0.0523598775598299; … ; 6.2482787221397;6.26573201465964;6.28318530717959;6.30063859969953}
The same array of Degrees now converted to Radians
You can learn more about how this style of formula works by reading the Formula Forensics Series where a number of similar formulas are used.
We can now use the Array of Radians to feed the Formula for the X and Y values
Looking at X: the X value of each point will be X = Circle Radius * Cos( t )
Where t is our array of Radians
So for Circle 1, the Maximum Circle the X Values will be
X_1: =Cos(t)*Max_Circle
In a blank cell C29 type: =COS(t)*Max_Circle press F9 not Enter
Excel will display ={14.9977154273459;14.9908624052864;14.9794430213186; … 14.9908624052864;14.9977154273459;15;14.9977154273459}
This is an array of the X Values of the Maximum Circle, all 360 of them.
You can check out the other X and Y values for the other circles yourself.
To add the Circular Grid lines to the chart, Right Click on the Chart, Select Data
This is the same Dialog we saw earlier
Select Add
Series name: =”Max Circle”
Series X values: =’1′!x_1
Series Y values: =’1′!y_1
Note: that we have added the worksheet name and the Named formula to the Series X and Series Y value fields. This serves to reference the Named Formula to this worksheet, sheet “1”.
Select Ok and add the Mid and Min Circles in a similar manner.
Your chart should now be similar to this:
Add Grid Annotation
Add Grid Annotation by adding 3 more series to the chart, 1 series for each annotation point.
We can put a point at the intersection of the 3 circles and the X Axis because we know the radius and the Y value = 0 so the 3 points will be at
(Min_Circle, 0)
(Mid_Circle, 0)
(Max_Circle, 0)
Once again Right Click on the Chart, Select Data
This is the same Dialog we saw earlier
Select Add
Series name: =”Min Annotation”
Series X values: =’1′!Min_Circle
Series Y values: ={0}
We can note that we have used the Named Formula for the Min Circle value as the X Value and that we have used a constant array for the Y value of 0.
Repeat this for the Mid and Max annotation points.
Format the Annotation Points
The 3 points you have just added to the chart may or may not be visible
The easiest way to find them is to either
Use the up/down arrow keys to scroll through the Chart series until you see it selected
Or
Select the Chart
Goto the Chart Tools, layout Ribbon and select the Min Annotation series from the drop down list:
If there is a marker showing, set the Marker Style to None
Close the Format Dialog and Right Click on the Marker, Add Data Labels
Select the Data Label and Change it from the Y Value to the X Value
Also change the Label Position to Above
Resize the Chart
Right Click on the outside of the Chart and select Format Chart Area
On the Size Tab, set the Height and Width to the same value
Select the Horizontal Axis and Delete it and repeat for the Vertical Axis
Your chart is now complete
DOWNLOAD THE ABOVE FILE
You can download the Example File used above: Excel 97/03, Excel 07/10.
OTHER CHARTS DONE IN EXCEL USING SCATTER CHARTS
As you have seen above the Scatter Chart can form the basis of your own custom Charts with the results being limited by your imagination.
Presented below are three Scatter charts where the authors have taken Scatter Charts to the extreme.
Hui’s – 3D Pendulums
In 2011, I produced an animated Scatter Chart consisting of 18 x 3D Pendulums in Excel which includes the ability to rotate the chart whilst the pendulums are swinging.
This is a Scatter Chart that consists of about 22 series, 18 for the Pendulums and a few others for the Frames and Axis.
The maths behind the pendulums locations and the rotations is all done via named formulas with a very simple macro driving the animation.
http://chandoo.org/wp/2011/07/06/3d-dancing-pendulums/
Excel Hero – Smith Chart
Daniel Ferry at Excel Hero.com has produced what I consider one of the most amazing charts in Excel I have ever seen.
I don’t make this statement just for the actual modelling of the Smith Chart or the use of Excel and Named Formulas in particular but also for the sheer Beauty that is displayed in the finished chart.
http://www.excelhero.com/blog/2010/08/excel-high-precision-engineering-chart-1.html
Frankens Team
The Frankens team has published a number of strange charts with a lot of them based on Scatter Charts.
https://sites.google.com/site/e90e50fx/home/creative-and-advanced-chart-design-in-excel
Please note that some of these charts use advanced excel techniques and are not for the feint hearted.
Yes the 3 charts above are all Scatter Charts, illustrating the incredible diversity that can be achieved using this tool.
WHAT CHART STYLES WOULD YOU LIKE TO SEE ?
What do you think of the techniques discussed above ?
What chart styles would you like to see ?
Let us know your thoughts to the above in the comments below:
64 Responses to “Understanding Variables, Conditions & Loops in VBA [Part 2 of 5]”
Nice 🙂 good explanation simple yet effective. The example was also not complicated..Looking forward for next part !
the download file is infected
virus
@Maria.. this file is perfectly alright. I think you have something else in your computer that is causing the trouble.
@Maria
Why do you say it is infected?
What message are you getting and where is the message from ?
The Input Box doesn't close maybe till all stores' values are entered (when "capture sales" button is pressed). Also, Alt+F11 doesn't work when Inputbox is open. Because of this, need to forcefully shut down excel. Is there any workaround for this problem?
Nice example Chandoo! I'm looking forward to joining VBA Class; these examples are such a great teaser! I have a big project at the office I'm looking to automate so I cant wait to save the time
Great....!!! I am a regular reader of your blog. I am new to VBA but you just made it as easy as eating a cake. Looking forward to your next awesome articles. You guys at Chandoo.org are wonderful.
Excellent tutorial with clear explanations of the variables etc.
I would only add some emphasis on the importance of using the
'comment lines to track what the code is doing.
This helps a lot if you need to modify it later, or if someone else needs to follow it up after you have lost the job!
I tend to be rather verbose myself due to short term memory problems 🙂
Chandoo, I know it's only a sample, but why didn't you indent your code? Makes it easier to read, especially when you're using conditionals and loops.
I use Smart Indenter (http://www.oaltd.co.uk/indenter/default.htm) to auto-format my code for me. So far no problems in Excel, and I also use it in MS Project modules and forms as well. Saves me the headache of trying to format everything by hand.
And ditto to Dave's comments: Early on in my career I didn't understand the importance of commenting, and after having to go back and revise and add new features to old code I'm kicking myself for not commenting as much as I should have.
Good post Chandoo. You are very best at teaching.
One small error to your notice - In the downloadable file "Store Number 5" is repeated 7 times. I hope you can change it an re-upload the file.
Regards,
Ravi.
Great work, omg ive always struggled to understand dim, loops and all but this is a wicked example! KEEP IT COMING 🙂
Excellent examples to illustrate what variables/conditions/loops are and what functions they serve. Good mental model.
[...] Understanding Variables, Conditions & Loops in VBA [...]
I'm a programmer by traiing, but Fortran and PL/1 were my languages in my day. Any recommednations on a good VBA reference book or manual.
If I wanted to write applications in VBA outside of Office 2007 or 2010, where do I buy a VBA compiler, linker or VBA interpretor for Windows 7?
[...] Understanding Variables, Conditions & Loops in VBA [...]
hi,
I have to create a Powerpoint whoes headline should be populated from the column of the XL sheet. Is this do able? if so could you please guide me. I actually do testing and take test evidence and store it in the PPT as slides,now i want to populate the step of test from the headline of every slide into the XL sheet which stores the Test script or from the test script to the PPT headline. which one is executable? i am totally new to macro and have faction of knowledge of it..
Hello,
I read your site daily. It is awesome. I need a VBA macro that will cycle through the options in a combo box (which you taught me to create), and print the dashboard for each sales manager in the combo box. Then stop looping. Any chance you can help? Thanks!
Best,
Amy
Good and thanks,
Great Tutorial its very useful.
Thanks,
siva
nice explanations
I need help developing a variable loop for the following code please. The two variables are the person's email (eMailID) in which to send the report and the place of service (POSc) to select in a pivot table of the report. Both variables are in an Excel table range as listed:
For Each POSc In Windows("Constants.xlsx").Sheets("ProvPOS").Range("AR3:AR74")
For Each eMailID In Windows("Constants.xlsx").Sheets("ProvPOS").Range("AU3:AU74")
Workbooks.Open Filename:="I:\Denials Monthly FYTD Resp.xlsx", _
UpdateLinks:=3
Windows("Denials Monthly FYTD Resp.xlsx").Activate
Sheets("DirMgr Resp").Select
ActiveSheet.PivotTables("PivotTable156").PivotFields("POS").ClearAllFilters
ActiveSheet.PivotTables("PivotTable156").PivotFields("POS").CurrentPage = _
(POSc.Value)
Sheets("Denials by Catg").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("POS").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("POS").CurrentPage = _
(POSc.Value)
Sheets("Top25 Reasons").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("POS").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("POS").CurrentPage = _
(POSc.Value)
ActiveWorkbook.SaveAs Filename:= _
"H:\Service Payor Mix\Denials FYTD " & (POSc.Value) & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=True
Sheets("Top25 Reasons").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Denials by Catg").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("DirMgr Resp").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("data").Select
Cells.Select
Selection.ClearContents
Sheets("data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("DirMgr Resp").Select
ActiveWorkbook.SaveAs Filename:= _
"I:\Denials\Denials FYTD " & (POSc.Value) & ".xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.SendForReview _
Recipients:=(eMailID.Value), _
Subject:="Please review your report: Denials FYTD.", _
ShowMessage:=False, _
IncludeAttachment:=True
ActiveWindow.Close
' Application.DisplayAlerts = True
Next
Next
End Sub
The above code runs, but does not do what I want it to do, i.e., use each variable in the range, "POSc", as the pivot table field selection "POS" to create a unique report for each person's "eMailID".
Thank you,
Mark
Hi,
Thanks for such a great website. It has been very useful to me. I have done an IF formula to calculate Tax in excel but would like to write it as a UDF in VBA, can anyone help pls? Please see the formula below. Pls note that if taxable income is 5,751,882 then tax = 1,605,565.
IF(TAXABLE INCOME<=150000,0,IF(TAXABLE INCOME<=450000,0.15*(450000-150000),45000))+IF((TAXABLE INCOME-450000)<300000, (TAXABLE INCOME-450000)*0.2,60000)+IF((TAXABLE INCOME-750000)>0,(TAXABLE INCOME-750000)*0.3,0)
Abdul
Try the following:
Function Tax(TI As Double) As DoubleIf TI <= 150000 Then
Tax = 0
ElseIf TI <= 450000 Then
Tax = 0.15 * (TI - 150000)
Else
Tax = 45000
End If
If TI > 450000 And TI <= 750000 Then
Tax = Tax + (TI - 450000) * 0.2
ElseIf TI > 750000 Then
Tax = Tax + 60000
End If
If TI > 750000 Then
Tax = Tax + (TI - 750000) * 0.3
End If
End Function
To use it copy the code and paste it in a Code Module in VBA
In excel simply use:
=Tax(Value)
or
=Tax(A1)
I hope the logic is correct but you can adjust to suit
@Chandoo, I was trying to join VBA Classes but getting a response that my card cannot be used to pay. I am using a Visa Debit Card from Sierra Leone. Will appreciate your help. In the meantime, Hui, can you pls help me with a udf to calculate taxable income?
The logic is: taxable income = Gross Salary - (Social Security Deduction + 220,000))
Looking forward to your response.
Thanks.
Thanks a ton, Hui.
It works perfectly.
Thanks once again. I was wondering, is it possible to do this in Access?
how is the dollar symbol automatically coming up ???
For doing this you need to select you all sheet by pressing ctrl+a.
After that you need to press ctrl+1 at your left side and in the last whenever you will put numberic figure in that sheet $ symbol autometically shown at the end of figure.
SORRY BEFORE YOUR LAST STEP I WAS FORGOTTEN TO TELL YOU THAT WHEN YOU PRESSED CTRL+1 YOU NEED TO SELECT CURRENCY AND AFTER THAT OK
NOW TRY AGAIN
It would have been much more benefitical to have a step by step tutorial to have us create this actual example from start to finish.
Perfect, your site is just about perfect......
i have a huge data and wish to sort out some desired data out of it......
what do you suggest me to use as sorting tool?
i know how to use pivot table but it is not resolving my problem as i have many fields
Please Check this, my condition value becoming zero when I run this macros..................
Dim Revision As Integer
Dim Purpose As String
Application.ScreenUpdating = False
' ---------------------- Removing Border ------------------
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A2").Select
' ----------------------- Looping -------------------------
Do Until IsEmpty(ActiveCell)
If Revision = 0 Then
Purpose = "C"
Else: Purpose = "R"
End If
ActiveCell.Offset(0, 1).Range("A1").Value = Revision
ActiveCell.Offset(0, 3).Range("A1").Value = Purpose
ActiveCell.Offset(0, 5).Range("A1").Select
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -4).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -5).Range("A1").Select
Loop
Application.CutCopyMode = False
Range("A2").Select
ActiveWorkbook.Save
End Sub
Hi Chandoo,
I'm new to VBA and this example was marvelous. Very simple and made lot of sense in ur explaination. Would surely go through all the other examples. It was excellent.
Thanks a ton.
How can I loop this little code in A1 I a number to add to A2 answer in Sub learnloop()
Dim aone As Integer
Dim atwo As Integer
Dim athe As Integer
aone = Range("a1").Value
atwo = Range("a2").Value
athe = aone + atwo
Range("A3").Value = athe
If athe < 100 Then
MsgBox ("learn about looping")
End If
End SubA3 in A4 and 5 other two number answer in a6 how loop apply here.
Good Day,
I need your help/assistance, because I need to do this report filtering only the Start Time and End Time of each Practitioner.
Report Date Practitioner ID Practitioner Name Start Time End Time
Hi Chandoo,
I use Excel spreadsheet a lot and find writing some basic logic statements that will generate a result for me. For example, in the above example of yours (i.e. 25 stores reporting revenues) I can do that in Excel spreadsheet using " =IF(....)".
My question is, how do I convert my knowledge of " =IF(...)" into VBA? Where do I start when I have a different scenario and thus a different " =IF(...) " logic?
I am not technical, and am only learning VBA from your tutorials (just finished lesson 1 🙂 ; and btw, your site is very helpful. Great Job!
Chandoo,
Your example spreadsheet does not work in Win.8.
message when I try to save the spreadsheet: Compatability Check; loss of functionality
how to learn easily coding/programming in VBA in excel which sources is useful
Also logic use in VBA and how are we better in vba
@Saad
Programming or making a Model in Excel is effectively the same thing
It is purely the format of the environment and the syntax of the language that you are working in that is different
You break a problem down into logical steps
where each step or group of steps represents generally a physical or data flow component from real life
Variables are simply cells (in the Excel Model) or Variables (in VBA) that can hold a value or string
eg:
In Excel
A1= 10 Sales of Apples
A2= 20 sales of Bananas
A3= A1+A2
=30 Total sales
In VBA
Dim Sales_of_Apples as Double
Dim Sales_of_Bananas as Double
Dim Total_Sales as Double
Sales_of_Apples = 10
Sales_of_Bananas = 20
Total_Sales = Sales_of_Apples + Sales_of_Bananas
=30 Total sales
VBA has a number of tools that allow more effective decision making and repetitive functions or loops to be performed a lot simpler than can be done using Excel
To learn,
1. Start with small problems and slowly get bigger by introducing new functionality and steps to your VBA
2. Look at other peoples solutions to problems and ask how/why they did what they did
3. Read a book on VBA, They typically walk you through from the basic steps to advanced steps in a logical sequence
Hi Chandoo,
Very good basic understing of VBA and Macro. However I am not clear how this programe will simulate the Macro.. I am not clear, whether program will run the excel or excel data will create the programme..? please clarify.
Hi,
I was able to go run the macro on my own and it worked. I notice the macro does not store the values to excel, is this expected? Is there a way I can do this?
Thanks,
Eric
Excellent tutorials Chandoo. I am new to VBA. I was wondering if you want to add another question, like "What is the store number?" before putting in the associated sales value, how do you do it?
Noob Alert!
Am I the only one who is having an issue with this example.
When i copy this code into VBA for a blank sheet and ensure that the range C7:C30 is as shown in the Gif
Store Number--Sales for the day--Reason for Deviation
1
2
3
4
5
6
7
8
......
when I enter a value for store 1 in the input box, excel overwrites the store number with the value i've just entered, rather than writing it in the "Sales for the Day" column and if there is a reason for deviation it gets written to the "Sales for the Day" column?
I thought I could fix this by editing the line of code
store.Value = InputBox("Sales for Store" & storenum)
to
store.Offset(, 1).Value = InputBox("Sales for Store" & storenum)
and editing line
store.Offset(, 1).Value = reason
to
store.Offset(, 2).Value = reason
Which sort of worked, but now asks every time "Reason for Deviation", regardless of Value?
#stumped
#helpaNoobweek
When I say it sort of worked. I mean that it does now correctly write the value to the ""Sales for the Day" column, but now asks for reason for each store?
Thanks
Hi Guys
My name is Elvis I would like to join the group as I do believe that this forum do have some to lean from, I'm using excel vs macros on a daily basis and the best part is that I never attend a formal course for both of them.
I hope and trust that there is a lot that I still need to learn more with your assistant through this forum.
Great work dude!
VBA simplified!!!
Look forward to get more free stuff here 😀
What change would I need to make in the code if I want the popup box to abort itself if I press the cancel button.
Dear chandoo team the article is awesome but I need more clarification how I delete blank row within data & special character.
Hi any body can tell me how to generate a mentor report of many students one by one with the help of vba code.pls give me the code.
Respected Sir,
Its really Helpful but as i coming from Telugu Medium Back Ground i am unable to understand completely so if possible if you provide it Telugu Language also it is very help full to the persons like me..
Thanks& Regards
Vivaan Kumar
Very Useful..Thank You..
Chandoo you are the man
Love this, can you just explain to me how the End If works
End If
storeNum = storeNum + 1
What is VBA doing during this process ? Why would the Integer StoreNum being itself + 1 end the program ?
Here is what I need to do. If I have a very long file with pairs of data in 2 columns for example:
A 1
B 2
C 3
D 4
and so on
I would like to arrange this data in 4 columns, so it looks like this:
A 1 B 2
C 3 D 4
How can I do that? I created a macro that will do it for the first 2 rows, but how do I repeat that (range) until the spreadsheet reaches a blank cell?
@Ratan
D1: =OFFSET($A$1,2*(ROW()-1),0)
E1: =OFFSET($A$1,2*(ROW()-1),1)
F1: =OFFSET($A$1,2*(ROW())-1,0)
G1: =OFFSET($A$1,2*(ROW())-1,1)
Copy D1:G1 down
Thank you for your suggestion. But I was looking to do this without having to copy the formula to the entire spreadsheet. Also, the number of rows from one spreadsheet to another may be different. So, is there a way to write the program for the first two rows and make the program repeat it (via range or some other function) until it reaches a blank cell when it will quit.
I am new to VB so, your suggestion of Copy D1:G1 down, is that a part of the macro, or you're saying copy it manually?
I had written a similar Macro in Lotus many years ago, and the statement in Lotus was \branch which took the operation to the top and repeated the function until it reached a blank cell when it quit. Unfortunately, there's no more Lotus.
Thanks.
hey can u explain me this code
"TRANSFORM Count(MyTMP_MRAntrag.MeldungsNr) " & _
"SELECT TMP_Zustandsklassifizierung.Zustand AS [Metric_MR_State] FROM TMP_Zustandsklassifizierung " & _
"INNER JOIN MyTMP_MRAntrag ON TMP_Zustandsklassifizierung.MR_State = MyTMP_MRAntrag.Zustand " & _
"WHERE ((MyTMP_MRAntrag.Produkt In " & Product & ") " & _
"AND ((MyTMP_MRAntrag.Priorität)>=0) " & _
"AND (MyTMP_MRAntrag.Datum <= #" & EndCycleText & "#) " & _
"AND (MyTMP_MRAntrag.ConfField1_Key in " & AffectedArea & ") " & _
") " & _
"GROUP BY TMP_Zustandsklassifizierung.Zustand " & _
"ORDER BY TMP_Zustandsklassifizierung.Zustand DESC , MyTMP_MRAntrag.Art " & _
"PIVOT MyTMP_MRAntrag.Art In ('Err','Imp','CR','NCI');"
iRow = 14
Hi Chandoo,
I tried to re-write the code by myself but got stuck here :
Sub ATTEMPT()
Dim start As Integer
Dim sales As Range
Dim reason As String
start = 1
For Each sales In Range("D1:D10")
sales.Value = InputBox("Sales for Store" & start)
If sales.Value 5000 Then
reason = InputBox("Why", "Reason", "Deviation")
sales.Offset(, 1).Value = reason
End If
start = start + 1
Next sales
End Sub
but the line with the condition has stuck without any reason and it shows debug error and does not go further beyond that?
I am new to this.
Thanks
Vidushi
@Vidushi
Can you ask the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file
put comparison sign between "sales.Value > 5000" (you need to put the condition correctly)
and then try...
Hi Guys,
I am New in VBA
I need your help to create VBA code for below case/example
I have created userform for Raw material entries with below details
For Example -
SR.No - 1
Date Of entry - 29-Mar-2020
Supplier Name - ABC India Ltd
Material Name - Deisel Engine
Material Number - ............
Now Condition is I want to create Material Code automatically in userform for each material on the basis of material receipt as below
Material Code For below Case should be - A100120B
Material name - Deisel Engine - A
Supplier Name - ABC india ltd - 1
Batch Code - for first batch - 001 (so on for remaining batches)
Year - 2020 - 20
Material Type -
please guide me
Regards,
Sachin Bhor
Mail - sachin22588@rediffmail.com , sachinb22588@gmail.com
Also i want create batch code for material automatically
Like - Batch Code - for first batch - 001, 002, 003, 004, 005,006
(so on for remaining batches)
Hi,
I need urgent help to get specific data from description field .
Like i want from excel Short description field search "Bandwidth" and if found then copy to the next blank row (exact to that row where it found).
I tried to record the macro but for any kind of changes it wont work perfectly.
If anyone can help me to solve this problem and make my report perfect.
@Rekh
Can you ask the question at the Chandoo.org Forums
https://chandoo.org/forum/
and attach a small sample file