rng1

  1. S

    check for missing records and paste to sheet

    hi all, I have the following code: For iCounter = 9 To iLast Set rng1 = ws.Range("G9:G5000").Find(ws2.Range("G" & iCounter).Value) If rng1 Is Nothing Then ws2.Range("A" & iCounter & ":" & "K" & iCounter).Copy ws.Range("A" & ws.Range("A" &...
  2. J

    Expanding a working code in Workbook_BeforeSave to more sheets with small alterations? VBA

    Hi experts! I'm fairly new to VBA to say the least, but I have managed to piece together a code that works and does exactly what I want it to. The code looks as follows: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Rng1 As Range Dim Rng2 As Range...
  3. T

    Type Mismatch Using SumProduct

    Hello, Let's say rng1 is some range like A1:A10, and rng2 is some range like B1:B10. I am trying to do the Application.WorksheetFunction.Sumproduct and it is giving me a type mismatch. I have: Application.WorksheetFunction.SumProduct(--(rng1 > 0), rng1, rng2) I only want to sumproduct...
  4. Z

    VBA run-time error 91, variable not set

    I'm now getting run-time error 91 with it highlighting my variavle rng1 as not being set. The code worked previously but now on re-opening the file isn't working. If someone could please let me know what I need to alter here in my code I'd greatly appreciate it. Dim x As Integer Dim y As...
  5. rhino4eva

    find and delete again

    Sheets("Sheet1").UsedRange Dim Rng1 As Range For Each Rng1 In Range("Z:Z").SpecialCells(xlBlanks).Areas Intersect(Rng1.EntireRow, Range("A:BT")).Delete shift:=xlUp Next Rng1 ok so I have used this code many times but need to modify it a little...
  6. S

    Error on Macro that paste special multiples a union range by a cell

    I am trying to create a macro that takes the value in cell X3 and paste special multiplies it to a lot of cells. I am getting error 424 object required. Sub IncreaseSalesBy()' ' IncreaseSalesBy Macro ' Increase sales by percent in X3 on Budget Workbooks ' ' Dim Rng1, Rng2, Rng3 As Range...
  7. D

    VBA Compare 2 columns and Return the difference in anoth column

    Hi I am trying to compare cells in 2 columns and return the value in the 3rd column on the same Row. This is what I have so far, which is the Ranges I need to use, and have several criteria Compare each cell between Rng1 and Rng2 and if return the value in the xRng Column if: -the cell Value...
  8. R

    Difficulty Subtracting Dates in VBA

    I can't believe I'm here for this...but I can't get this to work Dim Rng As Range Dim Rng1 As Range Set Rng = Worksheets("Sheet1").Range("D4") Set Rng1 = Worksheets("Sheet1").Range("D4") - 2 The value in "D4" is a date - The error I'm getting is an 'Object Required' Error on the Set Rng1...
  9. Ramballah

    Error in my code please help

    Hi all, I have this code in my sheet and it worked perfectly untill i added that line that i marked red. Somehow it wont activate. I tried to use the F8 function and it gave me this error: Run-time error '1004': Application-defined or object-defined error I don't know what to do. and i need...
  10. P

    Color selected filtered rows only

    Hi All, I am new in excel macro and stuck while creating a sheet. I have a excel where there are 2000 columns and i have to filter the data using column "test case name - Column D". once the filter is set then the selected rows need to colored and another column should be added in last with...
  11. M

    Delete all rows after copying in new sheet by vba

    HI i need to delete all rows after copying in new sheet. I am using below code last = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).RowSet rng = Sheets(sht).Range("A1:F" & last) Set rng1 = Worksheets("Need to be removed 0").Range("B2:B4") 'Sheets(sht).Range("F1:F" & last).AdvancedFilter...
  12. Nelson78

    VBA: Application.VLookup and, if found, offset

    Hello everybody. I'm struggling with the following job. Looking for a cell10 value in rng1, if the value is present, then overwrite cell10 with the value next to the left relating to the found value in rng1. Example: cell10 = "abc" If "abc" is present in rng1 (suppose in cell C27), then...
  13. I

    VBA help on conditional formatting.

    I was wondering if it was possible to be able to bold and italicize a name from a drop down box. I have a list of names on worksheet 3 that will get longer and shorter. I’ve named the range, Pro. Named Range =OFFSET(Pro!$G$1,0,0,COUNTA(Pro!$G:$G)). On worksheet 4 I have a grid with drop down...
  14. kelly mort

    Copy from all sheets except one

    I wanna copy from all sheets in my workbook except one of them. How do I fix this loop in the code below? Sub AnotherLoop_1063452() Dim r As Range, rng As Range, rng1 As Range, b As Boolean Dim arr As Variant Application.ScreenUpdating = False Set rng = Range("C1:C" & Cells(Rows.Count...
  15. kelly mort

    Code Adjustment for copying lookup results needed

    I am looking for a fix to this code so that it will look for the InputBox entry from column C and E instead of looking for them from just column C. Thanks in advance Sub AnotherLoop_1063452() Dim r As Range, rng As Range, rng1 As Range, b As Boolean Dim arr As Variant...
  16. kelly mort

    Copy range with data intead of entire row

    Hi, I wanna copy just the part of the rows with data . Here the data range is column B to Column H . How do I do that? The code down here copies the entire rows which I wanna avoid. Thanks Kelly Sub AnotherLoop_1063452() Dim r As Range, rng As Range, rng1 As Range, b As Boolean Dim arr As...
  17. S

    Workbook_BeforeClose event

    Hi, I am looking for some help in modifying the below code. Present Situation: Below code will not allow the user to close the workbook unless Rng1 is filled out. Requirement: The below code should be modified in a way that if all cells in the range are blank, user can close the workbook...
  18. M

    Auto Update Cell based on A Sheet Name (not current sheet)

    Hi All, I'm trying to put together a workbook with a lot of sheets. I want to be able to have a sheet called 'MASTER' with headers eg. Cells A1 to A20 (there will be many more than this!) I want Cell A1 to be linked to the tab name of Sheet2 I want Cell A2 to be linked to the tab name of...
  19. S

    Finding the mean value of a collection...or array?

    Greetings, I am adding integer values to a collection. After adding all the values meeting the add criteria I would like to take the average value of the integer and output it to a sheet. I am unsure of how to calculate the mean value of the contents in a collection. Would this be easier to...
  20. F

    What am i doing wrong? VBA

    Hi all, I have a subscript out of range error, but i cannot find the reason why. Whatever i do or change, it still comes back. Debug shows me range selection as an error. Can anyone assist me? char1 = "'" char2 = "/" char3 = "-" char4 = "," char5 = "." char6 = "ß" Set rng1 =...

Some videos you may like

This Week's Hot Topics

Top