Marc, I've been a member since 2014 and consistently seek help from this group while also trying to assist those who struggle with Excel, just like I do. I strive to be honest in my learning approach.
Regarding trying before posting, I actually don't, as I've had bad experiences with viruses on...
@p45cal
I should admitt it...Your meticulous analysis of problem statements has inspired me to be more deliberate in my thinking and avoid jumping to conclusions too quickly. Your dedication and expertise are truly admirable.Hat's off to you!
Okay...
I Think there was a misunderstanding in how the IFERROR function was used. Let's adjust the formula to avoid the reference back to cell A2 when encountering an error. We can achieve this by using a large number as the default value instead of IFERROR. Here's the revised formula...
Hello
Inhave changed by removing the extra , 0 at the end of the formula. This was causing the "too few arguments" error. Now the formula should work
=INDEX($A$2:$A$41471,MATCH(MIN(IF(($B$2:$B$41471=B2)*($C$2:$C$41471>=F2), $C$2:$C$41471-F2, IFERROR(1/0))))...
The above formula should find the closest matching product code within each Generic ID class to the 22nd percentile price. Adjust the range if your data extends beyond row 41471. Let me know if this resolves the issue.
Hello
Let's try refining the formula. It seems the issue might be related to how the conditions are applied. Let's adjust the formula to ensure it's correctly filtering based on the Generic ID and the price being above the 22nd percentile. Here's the revised formula for column G...
Hello
We can use the PERCENTILE function to find the 22nd percentile price for each Generic ID class. You can use the following formula in an empty column (let's say column F):
=PERCENTILE(IF($B$2:$B$41471=B2,$C$2:$C$41471),0.22)
Now, in another column (let's say column G), use the following...
Hello
I think want to use codenames of sheets from another workbook in your VBA code.
However, VBA won't recognize those codenames if the sheets are not present in the current workbook. Combining the workbooks is a valid workaround if keeping them separate isn't crucial for your workflow...
Hello
Based on your description, it seems like the issue might be related to how the slicers are updating when new data is added to the "Shipment Table" and the pivot table is refreshed....Am currently workingbon code separately...will share shortly
Try this !
Sub DataSplitWithHeader()
Dim asheet As Worksheet
Dim lastrow As Long
Dim myarray As Variant
Dim i As Long
Dim nextRow As Long
Dim targetSheet As Worksheet
Set asheet = ActiveSheet
Set targetSheet = Worksheets("DataTarget")
lastrow =...
Hello
After looking at code i think you have to replace the line
Sheets.Add.Name = myarray(i) with Worksheets("DataTarget").Activate.
Here is the modified version of your cod3.
Sub DataSplitWithHeader()
Dim asheet As Worksheet
Dim lastrow As Long
Dim myarray As Variant
Dim i...
Yes Marc....You're absolutely correct. If the worksheet is located in the same workbook as the VBA procedure, you can directly reference its codename without the need to declare a variable. Here's an example:
Sheet1_CodeName.Activate
Sheet2_CodeName.Activate
In this case, Sheet1_CodeName and...
Hello mate
If you want to refer to sheet codenames from nother workbook, you need to declare variables for those sheets correctly. Since the sheets might not exist at compile time, you can use the Object data type, which is generic and can refer to any object, including worksheets.
Here's an...
Good suggestion by Marc
Using the Text property instead of the Value property can be beneficial in certain cases, especially when dealing with formatted cells. If you're interested in using the Text property, you can modify the code like this:
Sub SaveWorkbookWithCellValues()
Dim ws As...
As per my understanding based on your discussions!
Sub SaveWorkbookWithCellValues()
Dim ws As Worksheet
Dim fileName As String
Dim path As String
Dim value1 As String
Dim value2 As String
Dim value3 As String
Set ws = ThisWorkbook.Sheets("Vessel Schedule")...
In the updated code, I added `.Orientation = xlHidden` inside the loop that iterates through each pivot field.
This line hides each pivot field from the pivot table layout, effectively preventing users from dragging and reordering them.
This should help
Sub RestrictPivotTable()
Dim pf As PivotField
On Error Resume Next
With ActiveSheet.PivotTables(1)
.EnableDrilldown = True
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each pf In...