# Search:

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

Page 1 of 6 1

1. ## Thread: Insert row, update total formula to include new row

by AlphaFrog
Replies
4
Views
120

### Re: Insert row, update total formula to include new row

Then have the macro insert a row above the last row. The last row will shift down. Copy the last row to the newly inserted row above. Then paste new data in the last row.
2. ## Thread: EXCEL VBA Find Headers and Copy Column from Sheet 1 to Sheet 2

by AlphaFrog
Replies
1
Views
82

### Re: EXCEL VBA Find Headers and Copy Column from Sheet 1 to Sheet 2

Sub CopyData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, dlc As Long, c As Long, col As Long
Dim colRng As Range, Rng As Range, Cell As Range
Application.ScreenUpdating = False
...
3. ## Thread: Insert row, update total formula to include new row

by AlphaFrog
Replies
4
Views
120

### Re: Insert row, update total formula to include new row

If you were to insert a row within I14:I20, the formulas would automatically expand to I14:I21 to accommodate the inserted row and keep the original cells.

Have your formulas include one blank...
4. ## Thread: Auto update cell price for inflation

by AlphaFrog
Replies
1
Views
100

### Re: Auto update cell price for inflation

A1 has the base price for say year 2010 and the predetermined inflation rate is 5%

This updates every year on Jan 1st

=FV(5%,YEAR(TODAY())-2010,0,-A1)
5. ## Thread: Offset Match Max - Priority Issue

by AlphaFrog
Replies
2
Views
98

### Re: Offset Match Max - Priority Issue

This is an array formula. Confirm with Control+Shift+Enter.

=INDEX(A11:L11,1,MAX(IF(MAX(A12:L12)=A12:L12,COLUMN(A12:L12),0)))
6. ## Thread: Ignore lines in a Macro

by AlphaFrog
Replies
3
Views
181

### Re: Ignore lines in a Macro

Sub SORTDATA()
'Sort Data on Seets PreK-Visitors

If UCase(ActiveSheet.Name) <> "WORKERS" And UCase(ActiveSheet.Name) <> "VISITORS" Then 'Not "WORKERS" And Not "VISITORS"
...
7. ## Thread: Checking if exists before executing

by AlphaFrog
Replies
3
Views
71

### Re: Checking if exists before executing

Sub CreateJobCard()

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long
Dim sheetCount As Long
Dim selectedCells
Dim newSheet As Worksheet

...
8. ## Thread: Ignore lines in a Macro

by AlphaFrog
Replies
3
Views
181

### Re: Ignore lines in a Macro

it should be AND not OR.

If ActiveSheet.Name <> "WORKERS" And ActiveSheet.Name <> "VISITORS" Then
9. ## Thread: Need row colour to be purple if a value is in a cell for each row

by AlphaFrog
Replies
11
Views
282

### Re: Need row colour to be purple if a value is in a cell for each row

Did you declare the variable at the top of the code like this?

Dim RowColor As Long
10. ## Thread: TextBox validation working but one line of code is not executed...

by AlphaFrog
Replies
7
Views
212

### Re: TextBox validation working but one line of code is not executed...

What does results in nothing mean?

Did you do this?

Private Sub tbnbna_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii 'Typed character
Case 32, 48 To 57,...
11. ## Thread: TextBox validation working but one line of code is not executed...

by AlphaFrog
Replies
7
Views
212

### Re: TextBox validation working but one line of code is not executed...

EDIT to the code above: Add ASCII character 32 to allow a space.

Case 32, 48 To 57, 65 To 90, 97 To 122 'Space, 0-9, A-Z, a-z
12. ## Thread: TextBox validation working but one line of code is not executed...

by AlphaFrog
Replies
7
Views
212

### Re: TextBox validation working but one line of code is not executed...

Private Sub tbnbna_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii 'Typed character
Case 48 To 57, 65 To 90, 97 To 122 '0-9, A-Z, a-z
Case Else
...
13. ## Thread: VBA to paste a formula till a specific number of columns to the right

by AlphaFrog
Replies
8
Views
207

### Re: VBA to paste a formula till a specific number of columns to the right

Sub MyFormula()
Range("A2").Copy Destination:=Range("B2").Resize(1, Range("A1").Value)
End Sub
14. ## Thread: TextBox validation working but one line of code is not executed...

by AlphaFrog
Replies
7
Views
212

### Re: TextBox validation working but one line of code is not executed...

Private Sub tbnbna_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.tbnbna.Value <> "" Then
If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) >...
15. ## Thread: VBA Chart colour change using case select and wild card

by AlphaFrog
Replies
1
Views
101

### Re: VBA Chart colour change using case select and wild card

Try something like this...

Sub ChangeMarqChartLColour()

Dim x As Integer

ActiveSheet.ChartObjects("Marq_ChartL").Activate

For x = 1 To...
16. ## Thread: Dynamic Chart Works Great until I grab data from different Sheet

by AlphaFrog
Replies
2
Views
106

### Re: Dynamic Chart Works Great until I grab data from different Sheet

How come you don't have the named ranges refer to the Data on Sheet2?
17. ## Thread: Need row colour to be purple if a value is in a cell for each row

by AlphaFrog
Replies
11
Views
282

### Re: Need row colour to be purple if a value is in a cell for each row

Maybe something like this (not tested)

Sub cmdCopy()
Dim wsDst As Worksheet, wsSrc As Worksheet, tblrow As ListRow
Dim Combo As String, sht As Worksheet, tbl As ListObject
...
18. ## Thread: Set focus back to seme TexBox

by AlphaFrog
Replies
4
Views
127

### Re: Set focus back to seme TexBox

Use the TextBox1_Exit event procedure. Maybe something like this. (you didn't explain how to test if the name exists with your data. The IF statement is psudo-code)

Private Sub...

by AlphaFrog
Replies
4
Views
128

### Re: Add character to the end of a string

Try something like this...

=IF(A1="","",IF(RIGHT(A1,2)="NC",A1,A1&"C"))
20. ## Thread: Excel Userform - Check specific textboxes to ensure that only one in each section has a value entered

by AlphaFrog
Replies
2
Views
139

### Re: Excel Userform - Check specific textboxes to ensure that only one in each section has a value entered

As you said, option buttons would be easier. Make the Captions of a grouped set of option buttons Yes, No, or N/A. This code checks the caption of the selected option and calculates the score.

...
21. ## Thread: Pass Userform Checkbox check as value into cell

by AlphaFrog
Replies
6
Views
196

### Re: Pass Userform Checkbox check as value into cell

It's just another shorthand version if IF. Highlight IIf in the code and press F1

by AlphaFrog
Replies
6
Views
268

### Re: Command Button Help

How is the dropdown list populated? Is there a reference to a Range of cells that has the list?
23. ## Thread: Overflowing text into next cell

by AlphaFrog
Replies
2
Views
98

### Re: Overflowing text into next cell

Select the unmerged cells S20 T20 U20 and Right-Click
Format Cells\ Alignment tab\ Horizontal \ Center Across Selection

This looks just like merged cells, but they are not merged.
24. ## Thread: Get up to 3 unique values based on two criteria and put in variables in VBA

by AlphaFrog
Replies
6
Views
171

### Re: Get up to 3 unique values based on two criteria and put in variables in VBA

'Example driver and month
Driver = "John": Mo = "March"
Data = Range("C2", Range("H" & Rows.Count).End(xlUp)).Value

For i = LBound(Data, 1) To...
25. ## Thread: Get up to 3 unique values based on two criteria and put in variables in VBA

by AlphaFrog
Replies
6
Views
171

### Re: Get up to 3 unique values based on two criteria and put in variables in VBA

Try something like this...

'Example driver and month
Driver = "John": Mo = "March"