1. I

    When to use hide/unhide vs visible = true/false

    I've written a handful of VBA and I always get caught up on when to use certain terminology, more specifically when it comes to hiding/unhiding. I've found that sometimes the correct method is to use something.visible = true / something.visible = false and other times I have to write...
  2. R


    MC allows for conveniently selecting multiple discontinuous cells as a single parameter within the syntax of formulas MC stands for "multiple cells". Many functions allow for selecting one cell or a continuous range of cells, and attempting to select multiple discontinuous cells through commas...
  3. S

    Macro For...Next Loop - use counter in FormulaR1C1

    I am trying load Picking Sheets with data from another sheet. I cannot workout the syntax to access the data. Receiving Compile error - Syntax. how do I access the other sheet? Sub Macro4() ' ' Macro4 Macro ' ' Dim OrderCount As Integer For OrderCount = 0 To 600 ActiveCell.Offset(0...
  4. V

    Brain is slow this morning, what's wrong with my syntax?

    cDelete = MsgBox("Are you sure that you want to delete this order?" & vbNewLine & "Ship Date:" & "" & me.ShipDate.Text "" & "- Customer:" & "" & me.CustomerBox.Text & "", _ vbYesNo + vbInformation, "Are you sure?")
  5. B

    Set Range = Worksheet.Range(Cells(..), Cells(..)) SYNTAX

    Hey Mr Excel Members I keep getting an error ("wrong number of arguments" or "method range failed for object _worksheet") on trying to add multiple different cells from different rows (row as long that can change) to a range. Dim TPsheet As Worksheet Set TPsheet =...
  6. V

    Escape Special Character in Column Name

    Hi all, need some syntax help here. I have these 3 lines of M code shown below (they're part of a larger query): #"Remove % from Load Factor Name" = Table.RenameColumns(#"Merge Periods with Comma",{{"Load Factor%", "Load Factor"}}), #"Divide Load Factor by 100" = Table.ReplaceValue(#"Remove %...
  7. U

    VBA FormulaArray Syntax/AutoFill/Set Ranges

    Hi all I am trying to do a few things here with VBA: Here's the source Worksheet named MoCMatrix ABCDEFGHIJKLMNOPQRSTUVW1HELPER ROW > Structural Integrity-MoCStructural Integrity-Substantiation DocumentStructural Integrity-StatementFlammability & Smoke-MoCFlammability & Smoke-Substantiation...
  8. K

    Formula works in sheet but syntax error pops in VBA

    Hi there, This formula works in the sheet but it won't compile in VBA due to a syntax error. Any tips? Thank you! As it appears in the sheet: =INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4),1,"")&"$"&$A8)*(Y8/100) As it appears in VBA: Sheets(shName).Range("C" & b + 1).Formula =...
  9. T

    Not seeing the syntax error

    I have the following formula that is giving me a #VALUE ! error and I've been looking at it so long I can't see where the syntax is wrong. Can someone please help? =IF(AND(Y17="Replacement2",Z8<3),"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print...
  10. A

    VBA Syntax For Dynamic Formula Statement For a Range of Cells

    Hello, I am struggling with the syntax of the following line in red ... With ws_srcdata src_lrow = .Cells(Rows.Count, 7).End(xlUp).Row src_rowcnt = src_lrow - 1 'populate calculated columns For r = 2 To src_lrow 'Z - Net Use Hours...
  11. W

    runtime error '-2147217900 #80040e14#'

    Hello, I've been trying to retrieve some data from an Access database, but I'm getting the above error message followed by 'Syntax error in ORDER BY clause.' Sub LookIntoAccess() 'This macro opens the Database From Excess.accdb database, creates and runs an SQL query. 'Then, it copies...
  12. D

    Structured References between workbooks

    Is it possible to use structured references between workbooks. I have a data work book which is pulling data from a table in a master sheet using row / column references and this is causing me an issue if a column gets added in the master sheet and I want to use structured references to get...
  13. I

    proper syntax for With Rng/Evaluate?

    i have a range(1000 rows) that i want to apply a change to. Its basically taking a variable and placing at the front of each string along with a comma. here is a before and after 6 of 1998 3-0-1 100.00% 14-18-2 43.75% 36, 6 of 1998 3-0-1 100.00% 14-18-2 43.75% variable was 36...
  14. Z

    syntax for range and/or cell declaration

    Trying to Set a range. What is the syntax if I want to use a named cell. "a1" and a variable like lastrow18. I have the variable lastrow18 set as a string and the value is stored as the last row of the column I am evaluating. Ex: Set range18 = Range("a1:lastrow18") What is the proper syntax...
  15. S

    Syntax error

    Hi, When copy the following direct into excel i dont get any errors, works perfectly but if i put it into a module and run it, i get syntax error... =average(INDIRECT($A2&"!a:a"))*SUM(INDIRECT($A2&"!B:B"))+1 But VBA it doesnt not allow me, what am i doing wrong? Range("E2").Formula =...
  16. A

    VBA syntax for allowing the input of a cell to be equal to a variable value

    Sorry for the confusing title, essentially what I am trying to accomplish is creating the VBA for the excel ceiling_math function so I can use it in a linear interpolation function I am creating. I would like to figure out the code to allow the unitcount and significance in this code (bolded) to...
  17. S

    Paste Value Syntax in VBA

    Struggling with this one... currentRow is a Range destinationWorksheet is Worksheet How do I copy with values and format by modifying this statement: currentRow.Copy destinationWorksheet.Cells(rowNumber, "b") New to VBA and struggling with Syntax....thanks!
  18. M

    Syntax for named range

    Seems like I'm missing something simple here to name a dynamic range. I'm trying to do this without creating a variable for the last row, etc. With wsLists.Range("A1") .Names.Add Name:="supplierSitesRng", RefersTo:=Range(.Offset(1, 0), .End(xlDown)) End With
  19. P

    vba correct syntax needed

    I have a program and I have some code that deletes columns from a table starting at week 2 and going to the right to the last column. I recorded the syntax from the Excel VBA recorder and it has quite a few lines. I want to condense it, so there aren't as many lines, as well as, make it more...
  20. S

    If Not IsArrow Then .List = 'What to syntax here to get full list of loaded ComboBox List when Form Initialized

    Hello Can anyone help me to get the right syntax which is mentioned in RED bold below Option Explicit Public idx As Long Private IsArrow As Boolean Private Sub UserForm_Initialize() Load UserForm1 UserForm1.Show vbModeless Dim rng As Range For Each rng In...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back