1. 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 =...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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 =...
  9. 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...
  10. 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!
  11. 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
  12. 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...
  13. 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...
  14. M

    Multiple COUNTIF(S)

    Hi im using excel 2016 i am having problems with my formula '=COUNTIFS(full_extract!E:E,full_extract!G:G"1",A1) count value from sheet column E:E, when column G=1 against the data in A1.. i get a syntax error thankyou
  15. M

    Access Query Expression Builder Concatenate IIF syntax, formula help?

    Hello and thank you in advance if you can help, I am trying to create a column in a query that compiles/concatenates information from other columns when certain criteria are met. The problem I am encountering is how to put it in terms of Concatenate IIf, or if that is even valid syntax within...
  16. J

    IF and ROUNDUP together

    I only want to apply the ROUNDUP function when N8<O8, otherwise return N8. This formula returns N8 regardless, even if I swap the < for >. Can someone help with the correct syntax? =IF(N8<O8,ROUNDUP((O4*N5),0),N8)
  17. D

    VBA syntax to repair corrupted file

    I need some help with some vba syntax. I have part of a procedure that opens another workbook, performs some actions then closes it. This is the code I have: Workbooks.Open (ThisWorkbook.Path & "\" & DocYearName) All the workbooks are stored in the same folder and the variable DocYearName...
  18. J

    INDIRECT Function

    I found a new handy function that allows me to write a formula that keeps the sheet name of the formula dynamic. It's pretty cool except that I barely understand how it works! So I have a formula that looks like this: =VLOOKUP(B3,'[2019 BOB Goal Calculation.xlsx]2019'!$C$8:$D$20,2,0) I would...
  19. jim may

    Syntax help

    In A Double-Click Event Macro I'm Having Syntax issues in getting the 2nd code line to "take" Lr = Range("C" & Rows.Count).End(xlUp).Row If Not Intersect(Target, Range("N & Target.Row & ":P" & LR)) Is Nothing Then What's the proper syntax? TIA, Jim
  20. X

    Generating a unique reference code for a document list using an excel macro

    Hi I am helping my local historical society in a voluntary capacity to get their website on line. It will contain a catalogue of 400-500 documents. I have a list of them which I can get into excel. In excel they appear as: column 1, a code for where they are in the filing cabinets, eg...

Some videos you may like

This Week's Hot Topics