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...

Hi I am trying to run some macros in a protected sheet. I believe using the UserInterFaceOnly:=True method will allow this and i have it in my Workbook_Open macro. But a simple operation of copying a relative formula does not work. Setup code: Private Sub Workbook_Open() Dim ws As Worksheet...

Hi guys! :) So, I've got this piece of code, which is working: ws.Range("A$" & rowCount & ":" & "AX1").FormulaR1C1 = "=[lol.xlsx]korr!RC" It simply copies A1 from one file to A1 of the other file, but with explicitly referencing the path (which is exactly what I need). However, as I have to...

I have a jagged array containing normal values and formulas in R1C1 notation and I want to write it into excel. I used following code for the same dataRange.Value2 = jaggedArray; But it produced unexpected results in case of column anchored formulas like '=$A1'. I got it fixed by rewriting...

Hi, How can I insert a variable for the sheet name. Example: I want sum cells A1:B10 in sheet1i=1 Range("A1").FormulaR1C1 = " =sum('sheet & i & '!R1C1:R10C2)" --> I know this is the wrong way to do it... Thanks in advance! Hurkk

Hello How can I easily blend text and formulas with FormulaR1C1? Ideally I don't want to have to double quote everything or escape numerous characters etc. Is there a neat way? Thanks

Hi all, I have a legacy macro that is used to read data from an excel file to a linked file to get info in to an Access database. The source files are protected, and I don't know where to find the passwords, but I need to get more of the information out of the source file and in to my database...

Trying to sub a variable into vlookup for the table array. This works: Sub vl() ' temporary fix for summer teach Dim vl1 As Range Dim vl2 As Range Dim vl1a As Range Set vl1 = Range("A:A").Find("C.I.") Set vl2 = Range("B:B").Find("17200") Set vl1a = Range(vl1, vl2) MsgBox...

Has anyone seen this and knows what's causing it and/or has a cure or workaround? I have a workbook that pops up a user form that base on the selection will update the associated workbook sheets. I have another workbook open at the same time that has several array formula in table that refer to...

I have a workbook that has multiple tables on each worksheet. Here is the formula that I have: Sub Test() Const WHAT_TO_FIND As String = "Application" Dim ws As Excel.Worksheet Dim FoundCell As Excel.Range Dim myTable As ListObject Set ws = ActiveSheet Set FoundCell =...

Hello, I need help with inserting a multiplication formula that is using two cell values in the formula IF another cell contains a specific value. If cell G1 = "1", then I want to insert a formula in cell H1 as follows G1*D1, and so on throughout the table of data. The table of data will...

Using Excel 2007 and VBA. I need to enter formulas into multiple cells of a worksheet referencing cells on another worksheet in the same workbook. The cells in the source worksheet are not contiguous, so copy/paste an existing formula on the destination worksheet will not work. I recorded a...

Hi all, I have a rather simple code where I want to split a row in two (say a bill). Some of the cells will contain the same information (e.g. date), which is just copied from the row I am splitting. When the row is split, I want some cells in the new row to be the residuals of the total amount...

This is a simplification of my problem. A1 has a number and B1 is selected. When I run this code it results in an error, why? Sub test() Dim x As Single x = 0.5 ActiveCell.FormulaR1C1 = "=RC[-1]*" & x End Sub

Why does the following code results in a error? (The code is a simplified version of my code, in reality I want a much bigger formula.) Sub test() Dim a As Range Set a = Selection Range("A1").Select ActiveCell.FormulaR1C1 = "=MAX(" & a & ")" End Sub

I have read many discussions on how to CONCATENATE or not to. Using public functions or entering the data in active cell. Right now I want it to select range D2 and combine all data in column D (not including blanks or duplicates) into Cell D2 with a delimiter (/) Range("D2").Select...

I'm trying to copy a value and paste it using R1C1 function. Most of my macro looks like this (which works): Range("A2").Select ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-10]=""value"",R[1]C[1],"""")" The above pattern is getting all of the values that I want into appropriate columns. Where I'm...

Hi, I'm trying to use VBA to put a formula in a cell which will average the values in the last 6 columns. The following works OK: TableSheet.Cells(WorkingRow, LastColumn + 1).Select ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-1])" (WorkingRow and LastColumn are integers) but I'd like to...

This looks like a fairly stupid question to ask and i've tried my way around the forum but couldn't find an answer that worked for me. I am passing using FormulaR1C1 with a SUMIF. When I pass a hardcoded value into the Criteria, it seems to be working fine. But when I try to pass a variable...

Good Morning All Any help with this issue would be greatly appreciated, i cannot seem to figure out why this wont work..? Dim refcell As String Dim Location As String Dim excelname As String Dim ext As String Dim r As String Dim lookuprange As String Dim cs As Integer Dim Boo As Boolean Dim...