# Search results

1. ### Distribution of Values

You do realise that this can't be solved using a linear distribution? Firstly there are 35 records with a value less than 123.9, that is records whose value is less than a value to get 150 points That uses up 5,250 points leaving 10,000-5,250=4,750 to be distributed to the remaining 13 values...
2. ### INDIRECT in dynamic arrays

You may want to try =Sum(INDIRECT(H2#&"!\$A\$1") ) Ctrl+Shift+Enter or =Sumproduct(INDIRECT(H2#&"!\$A\$1") ) Also supplying a sample file is the fastest way to get an accurate solution
3. ### Working with pie charts

Select your chart then select the Chart design Tab and try the various options Also select the chart and the press Ctrl+1 Explore the various options
4. ### How to draw an approximation graph in excel

I assume that A & B are 2 sets of Y values What is the X Axis values for the data?
5. ### Concatenate Cells According a condition

Have a look at teh following code Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Intersect(Range("B3:B35000"), Target) Is Nothing Then Exit Sub Dim break As Boolean break = False Dim myString As String myString = "" Dim i As Integer i = 1 Do If...
6. ### 3D Map Hourly Timeline don't come correctly

Add a field to your data called Date2 or anything else Make that field =A2+B2 Then in the 3D map Refresh data Then drag the new field name from the Field Picker into the Time dropdown Enjoy Time without a date assumes that the year is 0

8. ### cycle though formulas to show best result?

I am traveling for next few days Can’t look at it til mid next week Sorry
9. ### Help Required for Charting

Why not use a Water Fall Chart By doing that you can have an opening balance, a number of categories for Purchases and Sales and a Closing balance
10. ### Application.WorksheetFunction.VLookup code wrong

CStr() is Convert String so CStr(lRow1) converts the number lRow1 froma Number to a Text value for us in the remainder of the formula In VBA click on CStr and press F1 for an explanation of the function
11. ### Application.WorksheetFunction.VLookup code wrong

You are trying to execute the VLookup function in VBA by using Application.Worksheetfunction What you need to do is save the Formula as a text string representing the formula into the cells Change the line to read as below Range("C2:C" & lRow).Formula = "=VLookup(\$B2, \$K\$2:\$L\$" + CStr(lRow1)...
12. ### VBA to write a formula in column if cell not empty

I have changed the Worksheet event that is used to trigger the code and added a few tweaks Replace the existing code with the code below: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False On...
13. ### cycle though formulas to show best result?

Thanx Harry Can you please explain what A1:A4 do and what range of values for each cell are allowable?
14. ### VBA to write a formula in column if cell not empty

That code has to be in the Marciela Worksheet Object You also have to save the file a s a *.xlsm or *.xlsb file type
15. ### cycle though formulas to show best result?

Harry There are a few ways to tackle this style problem Can you post a sample file as an example of what you want?
16. ### VBA to write a formula in column if cell not empty

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub If Target.Value <> "" Or Target.Value <> " " Then Target.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)" End Sub
17. ### VBA to write a formula in column if cell not empty

Aggie81 Try the following: Sub x() Dim LR As Integer LR = Range("A" & Rows.Count).End(xlUp).Row Dim c As Range For Each c In Range(Cells(5, 1), Cells(6, 1)) If c.Value <> "" Then c.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)" Next c End Sub
18. ### VBA Code Gone!

Once again hard without seeing it Remember that Excel files are simply fancy Zip files Some data like straight data and tables stores really well Other data like Pictures or Logo's don't store well Also avoid Pivot tables where you add data to a Data Model
19. ### VBA Code Gone!

Do you have a backup ? Can you share the file here ?
20. ### Required to run Macro only if d13 has text purchase selected

I have given you enough assistance in the above to help you Please give it a go yourself first
21. ### Required to run Macro only if d13 has text purchase selected

It is not really clear what you are after but you have a few options Sub Purchase() If Sheets("PURCHASE").Range("D3").Text <> "Some text" Then Exit Sub 'Put the rest of the code here End Sub or Sub Purchase() If Sheets("PURCHASE").Range("D3").Text = "Some text" Then...
22. ### VBA Code Gone!

Have a read of this: https://superuser.com/questions/1401664/excel-vba-macros-have-disappeared
23. ### VBA Code Gone!

Paul Did you start from a *.xlsx or *.csv etc file and then add your code? If you didn't change file types to a *.xlsm that is why your code is gone
24. ### Two Old School Ways To Export Cells To A Fixed Length Text File

I'll do it over the weekend and then send you a link to review before publishing
25. ### Unique Data Value Count in Excel macro needed

something like: =SUM(1/COUNTIF(OFFSET(A2,0,0,COUNTA(A2:A1048576),1),OFFSET(A2,0,0,COUNTA(A2:A1048576),1))) Ctrl+Shift+Enter