VB:The dataset (strings) are copied to the proper sheet, a new column is inserted, the unique values are transferred to column A and the source column is deleted. When I attempt to run this through though, I get the error method range of object worksheet has failed. Looking at the sheet, the filtered data has been deleted and so the sheet is blank. I assume the script fails because there is no dataset on which to search for that last cell.rAppID.Copy With wsTotals With .Cells(1) .PasteSpecial xlValues End With End With 'Set rTotalsID = ActiveCell.CurrentRegion 'Add extra Column, "A" becomes "B" With wsTotals .Columns(1).EntireColumn.Insert .Rows(1).EntireRow.Insert .Range("b1").Value = "App IDs" End With Set rBEnd = Range("B65536").End(xlUp) Set rBTemp = Range("b1", rBEnd) 'Filter out duplicates and copy unique list to "A" ' .Range("A1").CurrentRegion.Clear rBTemp.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsTotals.Range("A1"), Unique:=True 'Add extra Column, "B" becomes "A" wsTotals.Columns(2).EntireColumn.Delete Set rTotalsID = wsTotals.Range("A1", Range("A1000").End(xlUp)) Set rTotalsKwh = rTotalsID.Offset(0, 1)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Thanks in advance.FCall() Worksheets("PreFinal").UsedRange.Copy Worksheets("Final").Select Worksheets("Final").Pastespecial Columns("F:G").Delete End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Can someone help me with this? I've tried different variations, but would only get a circular reference or some other cell that is not the active cell. I also tried to create an If Then Else statement, but didn't get any results there either. Here's the other variation I did:Value4 = InputBox(prompt:="Please enter annual salary or hourly rate.") ActiveCell.FormulaR1C1 = Value4 ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(SEARCH(""."",rc)),rc,rc*2080)" ActiveCell.Next.ActivateIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:I got this error msg: Compile Error: Expected list separator or )Value4 = InputBox(prompt:="Please enter annual salary or hourly rate.") ActiveCell.FormulaR1C1 = Value4 If IsError Search(""."", Value4) Then ActiveCell = Value4 Else ActiveCell = Value4 * 2080 EndIf ActiveCell.Next.ActivateIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sub FillRange() Dim Count as Integer For Count = 1 To 100 ActiveCell.Offset(Count -1,0) = Rnd Next Count End SubAnd it works perfectly, except, as expected, it gives me random numbers between 0 and 1. I'm trying to rewrite it to give me consecutive whole numbers from 1 to 100. Is there a simple way to write that?
in the code belowi want this macro to run when i click the button
This is to assign the macro HideCrews to each of the form buttons on the worksheets
Sub AssignMacro() Dim i As Integer With ActiveSheet For i = 1 To .Shapes.Count If .Shapes.Item(i).Type = msoFormControl Then .Select If Selection = xlButtonControl Then .Shapes.Item(i).OnAction = "HideCrews" End If End If Next i End With End Sub
Sub HideCrews() Dim i As Integer Dim j As String Dim Col As String Dim Sht As String Application.ScreenUpdating = False Col = Sheets("Data").Cells(8, 1).Value Sht = ActiveSheet.Name Sheets("Data").Visible = True Sheets("Data").Select Sheets("Data").Range(Col & "3").Select Selection.End(xlDown).Select j = ActiveCell.Row For i = 3 To j On Error Resume Next Sheets(Sht).Select If (Sheets("Data").Cells(i, Col).Offset(, 1).Value = "0") Then Sheets(Sht).Range(Sheets("Data").Range(Col & i)).EntireRow.Hidden = True End If Next i Application.ScreenUpdating = True End SubHow do i fix this error?
VB:App_WorkbookBeforeClose calls a sub on Module1 that in turn calls another sub, which, in part, adds a sheet if a condition is met.Workbook_Open() Set App = Application End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:The code works as intended whenever a workbook is manually closed. However, when triggered by a workbook being closed programatically, it does not work. Stepping through the code in break mode it appears that the code is executed, as the above line of code is run and no errors are thrown, but when it completes, the sheet has not been added.ActiveWorkbook.Sheets.Add after:=ActiveWorkbook.Sheets(Sheets.Count)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:I have marked the line that I have narrowed the error down to. Weird note, whenever I step through the code in break mode movingg one instruction at a time I do not get the error. I also don't get the error when I enter a breakpoint before the line (at some point after the chart is created) then I run the rest of the code at full speed. I don't think it is imoprtant, but I am getting the data for the chart on a separate worksheet named "dataSheet" but it is not the active sheet in my workbook. Any suggestions?'Create the graph ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=dataSheet.Range(sourceDataStart, sourceDataEnd) ActiveChart.ChartType = xlLine ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "=""Total Sightings""" ActiveChart.SeriesCollection(1).Values = dataSheet.Range(line1DataStart, line1DataEnd) ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(2).Name = "=""Closed Sightings""" ActiveChart.SeriesCollection(2).Values = dataSheet.Range(line2DataStart, line2DataEnd) ActiveChart.SeriesCollection(2).XValues = dataSheet.Range(xValuesStart, xValuesEnd) ActiveChart.Parent.Width = 500 ActiveChart.Parent.Height = 200 'set the chart, x, and y axis titles Dim xAxis As Axis Dim yAxis As Axis Set xAxis = ActiveChart.Axes(xlCategory) xAxis.HasTitle = True xAxis.AxisTitle.Caption = "Work Week" Set yAxis = ActiveChart.Axes(xlValue) yAxis.HasTitle = True yAxis.AxisTitle.Caption = "Sightings" yAxis.MajorUnit = 1 ActiveChart.HasTitle = True 'GETTING MY ERROR ON THIS LINE ActiveChart.ChartTitle.Text = "AN Presightings Per Work Week" 'create a pricture copy of the graph and delete the original ActiveChart.CopyPicture dataSheet.Paste Destination:=dataSheet.Range("Q17") ActiveChart.Parent.DeleteIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:The 3rd and 4th lines are highlighted when i view the code in break mode.With Range("G13:G2000").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=OR(AND($G13""Completed"",$I13"",AND($G13""Closed"",$I13"")))" Selection.FormatConditions(1).Interior.ColorIndex = 3If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines