Posts by Sarah Brown86

Kyle Johnson2 said:Here is the second part of the code, picking up right where the first bit left off.
Code:

Range("D20").Select
Selection.Copy
Range("D21").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C21").Select
ActiveCell.FormulaR1C1 = "5"
Range("D21").Select
Selection.Copy
Range("D22").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C22").Select
ActiveCell.FormulaR1C1 = "17"
Range("D22").Select
Selection.Copy
Range("D23").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C23").Select
ActiveCell.FormulaR1C1 = "19"
Range("D23").Select
Selection.Copy
Range("D24").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C24").Select
ActiveCell.FormulaR1C1 = "14"
Range("D24").Select
Selection.Copy
Range("D25").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C25").Select
ActiveCell.FormulaR1C1 = "12"
Range("D25").Select
Selection.Copy
Range("D26").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C26").Select
ActiveCell.FormulaR1C1 = "11"
Range("D26").Select
Selection.Copy
Range("D27").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C27").Select
ActiveCell.FormulaR1C1 = "24"
Range("D27").Select
Selection.Copy
Range("D28").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C28").Select
ActiveCell.FormulaR1C1 = "20"
Range("D28").Select
Selection.Copy
Range("D29").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C29").Select
ActiveCell.FormulaR1C1 = "4"
Range("D29").Select
Selection.Copy
Range("D30").Select
Selection.PasteSpecial paste : = xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C30").Select
ActiveCell.FormulaR1C1 = "11"
Range("C31").Select
select ActiveWindow.SmallScroll down : = -18 range
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,VLOOKUP(RC[-1],R1C6:R6C7,2,TRUE),"""")"
Range("D2").Select
Selection.AutoFill Destination : = range("D2:D30"), Type:=xlFillDefault
Range("D2:D30").Select
Range("A1:D30").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("F1:G6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("J2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C4:R39C4,R[-1]C)"
Range("J2").Select
Selection.AutoFill Destination : = range("J2:M2"), Type:=xlFillDefault
Range("J2:M2").Select
Range("A1:D30").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial paste := xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Range("A1:D1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$30").AutoFilter Field:=4, Criteria1:=Array("2", _
"3", "4", "5"), Operator:=xlFilterValues
End Sub

is the "program" I think would handle the task, at least in my estimation

Benjamin Wilson7 said:For the first round, she nested an IF (using AND) and solved the issue. Now we’ll see if he actually understands how function nesting works.

Nicholas King said:I suspect when they say "write a program," they're actually referring to VBA. 🙂

Kyle Johnson2 said:did you check out this tutorial

- Sales Tax in Microsoft Excel

Nicholas King said:The screenshot is actually a bit baffling. You've got entries for 200 and 300, yet the total sales tax is sitting at 20. 😕

I'm not entirely following the issue here. If E12, F12, and G12 are the cells where you input the price inclusive of sales tax, then H12 should be 0.22*(E12 + F12 + G12)/1.22, and I2 would be E12 + F12 + G12 - H12.

Naturally, if you're dealing with a different sales tax rate, those 0.22 and 1.22 figures won't apply.

Nicholas King said:Post your attempt at a solution, then people will be able to see what's going wrong.

Kyle Johnson2 said:@LordByron already provided the solution along with a full explanation. If you really want to record a Macro "by yourself," then take a look at how that's done—the process is identical for Microsoft Word as well.

- So, you want to record a Macro step by step? It’s actually quite straightforward once you get the hang of it, though people tend to overcomplicate the process. If you're looking to automate those repetitive tasks in Microsoft Office—specifically within Excel—you just need to follow a specific sequence. You aren't exactly "filming" a video of your screen; rather, you are telling the software to watch your every move and write down the code for you. First, you need to make sure the Developer tab is visible in your ribbon. If it isn't there, you'll have to go into your options and enable it. Once that's set, you simply hit the "Record Macro" button. From that moment on, the software acts like a silent observer. Every click, every cell change, and every formula you enter is being logged. A little tip from my experience: try to be very deliberate with your movements. If you're clicking around aimlessly or making mistakes, the Macro will dutifully record those errors too, and then you'll be stuck trying to fix a broken script later. It's much better to perform the task perfectly the first time. When you're finished, you just stop the recording. The steps are stored, and you can run them whenever you need to replicate that exact same workflow. It saves an incredible amount of time if you do it right. Simple enough, really.
- Recording a macro.

By the way, here is the complete Macro code for all the palatales. Of course, you can strip out certain sections if needed, just like @LordByron suggested earlier.

Code:

Sub Macro1()

'
' Macro1 Macro
'
'
ClearFormatting Selection
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "C"
.Replacement.Text = "C"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Execute Replace
With Selection.Find
.Text = "C"
.Replacement.Text = "C"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Execute Replace
With Selection.Find
.Text = "Z"
.Replacement.Text = "Z"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Execute Replace
ActiveWindow.ActivePane.VerticalPercentScrolled = 0 Selection
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "D"
.Replacement.Text = "DJ"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Execute Replace
With Selection.Find
.Text = "S"
.Replacement.Text = "S"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Execute Replace
ActiveWindow.ActivePane.VerticalPercentScrolled = 0 Selection
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "S"
.Replacement.Text = "S"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
End Sub