VB:Macro2() ' Sheets("raw data").Select If Range("AB2").Value "NA" Then ' this variable is ("employee numner" + Points) and it is used to find the right worksheet to insert values into Dim employee1 As String employee1 = Range("AB2") ' this variable is ("employee number") and it is used to find the right worksheet to pull data from to populate the ("employee number" + points worksheet.) Dim employeedat1 As String employeedat1 = Range("AA2") ' stuck, can't get the COUNTIF statement to pull data from the range specified in the worksheets(employeedat1) - ' Need to figure out how to specify a range from another worksheet in the COUNTIF statement. Sheets(employee1).Select Range("D3").Select ActiveCell.FormulaR1C1 = "=COUNTIF([COLOR=red]'worksheets(employeedat1)'!k2:k4000[/COLOR], ""
Its fine deleteing the row, but when I go back and select this sheet again I get an error with the selection.even though I have the exact same line of code just before and it doesnt give me an error.
Heres a snipet of the code but you guys can take a look at the actual file for the full thing.Sheets("Total WO Table").Visible = True Worksheets("Total WO Table").Select Sheets("Total WO Table").Move Before:=Sheets(4) Worksheets(Worksheets.Count).Select nameD = ActiveSheet.name 'Name of last sheet as variable ActiveWindow.SelectedSheets.Delete 'Deletes last sheet Worksheets("Total WO Table").Select '<--- This is where error occurs.
Sub Macro_2_Pass_Fuel_Solver() Dim i As Long Application.ScreenUpdating = False i = 6 Do Until IsEmpty(Range("A" & i)) SolverReset SolverAdd CellRef:=Range("I" & i), Relation:=1, FormulaText:="$I$2" 'sets maximum fuel vol tank 1 SolverAdd CellRef:=Range("J" & i), Relation:=1, FormulaText:="$J$2" 'sets maximum fuel vol tank 2 SolverAdd CellRef:=Range("K" & i), Relation:=1, FormulaText:="$K$2" 'sets maximum fuel vol tank 3 SolverAdd CellRef:=Range("I" & i), Relation:=3, FormulaText:="$I$1" 'sets Min fuel vol tank 1 SolverAdd CellRef:=Range("J" & i), Relation:=3, FormulaText:="$J$1" 'sets Min fuel vol tank 2 SolverAdd CellRef:=Range("K" & i), Relation:=3, FormulaText:="$K$1" 'sets Min fuel vol tank 3 SolverAdd CellRef:=Range("W" & i), Relation:=2, FormulaText:=0 'says sum of three tanks must equal total fuel vol SolverOptions MaxTime:=1000, Iterations:=1000, Precision:=0.000001, AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, IntTolerance:=2, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True SolverOk SetCell:=Range("v" & i), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("I" & i & ":J" & i & ":K" & i) SolverSolve userFinish:=True i = i + 1 Loop Application.ScreenUpdating = True End Sub
VB:I need to replace the Range("DN:DN") as this might not always be the column I'm looking for.85") / TotalIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:At this stage I simply want to see the code triggered so that I can see how it works. I will then make my required changes.Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Target.Address = "$A$1" Then 'Ensure target is a number before multiplying by 2 If IsNumeric(Target) Then 'Stop any possible runtime errors and halting code On Error Resume Next 'Turn off ALL events so the Target * 2 does not _ put the code into a loop. Application.EnableEvents = False Target = Target * 2 'Turn events back on Application.EnableEvents = True 'Allow run time errors again On Error Goto 0 End If End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sub WorksheetCollection Dim ws As Worksheet Dim NumberOfTabs: NumberOfTabs = 0 Dim ws1 As Worksheet 'should I declare 10 worksheet variables? Is there a simpler way? Dim ws2 As Worksheet Dim ws3 As Worksheet Dim ws4 As Worksheet Dim ws5 As Worksheet Dim ws6 As Worksheet Dim ws7 As Worksheet Dim ws8 As Worksheet Dim ws9 As Worksheet Dim ws10 As Worksheet Dim OrigSelectedTabs As Collection 'is collection appropriate, or is there a better type of object to be set equal to ActiveWindow.SelectedSheets? NumberOfTabs = ActiveWindow.SelectedSheets.Count Set OrigSelectedTabs = ActiveWindow.SelectedSheets 'this chokes up the macro For Each ws In ActiveWindow.SelectedSheets 'this will cycle through all selected tabs x = ws.name 'here I would like a simple way to identify the name of each tab and store for later use Next End SubThank you in advance for considering this.