# Search:

Type: Posts; User: theBardd; Keyword(s):

Page 1 of 6 1

## Search: Search took 0.04 seconds.

by theBardd
Replies
7
Views
211

### Re: Need help with CONCAT function

Try this

=SUBSTITUTE(B53,"Dale",IF(personal_info!C9=0,"Name",personal_info!C9))
2. ## Thread: Checking a Directory for a new file name that contains specific text

by theBardd
Replies
1
Views
89

### Re: Checking a Directory for a new file name that contains specific text

Try this

Public Sub test()
MsgBox CheckFileExists("C:\Users\Bob\Documents\Projects\_8 Community\Forums\VBAExpress", "65382")
End Sub
Public Function CheckFileExists(ByVal Path As...
3. ## Thread: Formula to pull up column/row head based on cell in data set

by theBardd
Replies
4
Views
115

### Re: Formula to pull up column/row head based on cell in data set

Try

=INDEX(B1:E1,MIN(IF(B2:E5=10,COLUMN(B1:E1)-MIN(COLUMN(B1:E1))+1,"")))

=INDEX(A2:A5,MIN(IF(B2:E5=10,ROW(A2:A5)-MIN(ROW(A2:A5))+1,"")))

both array formulas
4. ## Thread: USER FORM : ListBox population based on another ListBox value selection [VBA]

by theBardd
Replies
9
Views
197

### Re: USER FORM : ListBox population based on another ListBox value selection [VBA]

This should do it

Dim rng As Range
With ActiveSheet

Select Case Me.ListBox1.Value

Case "Rated Current_Order set 1": Set rng = .Range(.Range("G3"),...

by theBardd
Replies
5
Views
205

You could do it with Power Query, but here is a VBA solution

Dim firstdate As Date, lastdate As Date
Dim numrows As Long, numdates As Long, numinsert As Long
Dim lastrow As...
6. ## Thread: Is Power Query what I need?

by theBardd
Replies
3
Views
812

### Re: Is Power Query what I need?

In the example that you give, it would be simple to write some VBA that adds a helper column with a formula that tests whether that exception criteria is met or not, and then delete those rows that...
7. ## Thread: Sequential Numbering Positive/Negative Amounts

by theBardd
Replies
1
Views
224

### Re: Sequential Numbering Positive/Negative Amounts

Yes

=IF(A2>0,"POS"&TEXT(COUNTIF(\$B\$1:\$B1,"POS*")+1,"000"),"NEG"&TEXT(COUNTIF(\$B\$1:\$B1,"NEG*")+1,"000"))
8. ## Thread: Query Load to -Option of Pivot table report not coming

by theBardd
Replies
3
Views
627

### Re: Query Load to -Option of Pivot table report not coming

You want to add it to the datamodel as well to get that option in the pivot.
9. ## Thread: Parameter in ODBC PowerQuery

by theBardd
Replies
5
Views
440

### Re: Parameter in ODBC PowerQuery

The query might be slower, I guess it depends upon how much data you have. I am not sure if query folding would apply, my guess is that with an ODBC query it does not, so maybe doing it in the query...
10. ## Thread: Parameter in ODBC PowerQuery

by theBardd
Replies
5
Views
440

### Re: Parameter in ODBC PowerQuery

You could pull in all of the data from the data from the ODBC query, omit the WHERE clause. Filter he columns for some value, just to generate the M code. Then set variables to the values in the...
11. ## Thread: Excel power query

by theBardd
Replies
1
Views
419

### Re: Excel power query

In the code generated when you load and transform the files, you should see a step called 'Removed Other Columns' or similar. That should have a Table.SelectColumns function, just add ', "Folder...
12. ## Thread: Get & Transform with a parameter from Access query

by theBardd
Replies
1
Views
427

### Re: Get & Transform with a parameter from Access query

Pull back all of the data from Balances and then use Power Query to filter to your required CorpId.
13. ## Thread: Converting IF statement to Power BI

by theBardd
Replies
2
Views
780

### Re: Converting IF statement to Power BI

same as Excel really.

if [Field 1] <> [Field2] then if [Field 1] > 0 then [Field 1] else [Field2] else if [Field2] = 0 then "N/A" else [Field2]

by theBardd
Replies
4
Views
131

### Re: new name

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

filename:=Activesheet.Range("C2").Value, & ".pdf" _

OpenAfterPublish:=False, _

by theBardd
Replies
10
Views
256

### Re: EOM formula

I would recommend that you decide what should happen if there something other than nnEOM or nnFI in J4, and we build the formula to act accordingly.

by theBardd
Replies
4
Views
131

### Re: new name

Is C2 on that sheet?

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Activesheet.Range("C2").Value, _
OpenAfterPublish:=False, _
IncludeDocProperties:=True,...

by theBardd
Replies
1
Views
106

### Re: VBA Request

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsc_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B:B, D:D, F:F")) Is Nothing...

by theBardd
Replies
10
Views
256

### Re: EOM formula

I guess it would be

=IF(RIGHT(J4,2)="FI",C4+SUBSTITUTE(J4,"FI",""),IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM","")))
19. ## Thread: IFS statement with Data Validation

by theBardd
Replies
2
Views
111

### Re: IFS statement with Data Validation

Data Validation doesn't auto-populate, it just checks that the input is valid, or provides a list to select from.

by theBardd
Replies
10
Views
256

### Re: EOM formula

=IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM",""))

by theBardd
Replies
29
Views
349

### Re: Vba loop

Do you know how to build a loop?

Do you know how to terminate the loop?

Do you know how to use the inputbox?

Do you know how to use variables?

Do you know how to increment the variable?
22. ## Thread: Dynamic Average Selling Price Pivot

by theBardd
Replies
6
Views
176

### Re: Dynamic Average Selling Price Pivot

I am failing to see why a pivot doesn't work for this.
23. ## Thread: Using SUMPRODUCT to SUM a Defined Range

by theBardd
Replies
5
Views
215

### Re: Using SUMPRODUCT to SUM a Defined Range

Are the names similar size?

Can you post a sample of the data and tell us what result you want to make it clearer?
24. ## Thread: SQL Query on PowerPivot Data Model through VBA?

by theBardd
Replies
3
Views
538

### Re: SQL Query on PowerPivot Data Model through VBA?

As far as I am aware, whilst you can do a lot of stuff with the connections in VBA, the actual model is not exposed to VBA (and remember, it is an OLAP cube!).

You could build your dashboard using...

by theBardd
Replies
13
Views
388

### Re: counting

Sub countmontecarlo()
Dim cell As Range
Dim lastrow As Long
Dim n As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

...
Results 1 to 25 of 150
Page 1 of 6 1