I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?
I then need to enter the month labels in the new columns row 8.
My problem is trying to reference off the "This Year" column.
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I want to sum the values of two userform text boxes however I assume they are stored as strings so I get 1 + 2 = 12 - how do you convert strings to integers in VBA? It seems as though it's different to VB where you'd just use convert.toInt16() etc
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro. But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile. Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile. The next target file in the folder is opened and the actions are repeated in this second target file. For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook. The error message i get is: "Run-time error 91:Object variable or with block variable not set." When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
Column A | Column B | Column C Los Angeles | Fire Dept | 3 Los Angeles | Health Services | 12 New York | Fire Dept | 8 New York | Health Services | 22 New York | Internal Services | 100 New York | Public Works | 7 Chicago | Health Services | 15 Chicago | Public Works | 56 Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept Health Services Internal Services Public Works Social Services
When i try to run the code below i get the error message - object variable or with block variable not set-
Sub REFRESHXX()
'LIST Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select Selection.AutoFilter Field:=1, Criteria1:="1" 'SET RANGE Dim sFormula1 As String Dim sFormula2 As String Dim sCell1 As String Dim sCell2 As String Dim sSheet1 As String Dim sSheet2 As String Dim r As Range Dim MyRange As Range 'for testing
With Sheets("Points") sFormula1 = .Range("CY1").Formula sFormula2 = .Range("CY2").Formula End With
'FORMULA IN R1C1 STYLE strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))" 'ENTER FORMULA IN ALL CELL RANGES r.FormulaR1C1 = strFormula 'REDUCE TO VALUES Dim ar As Range 'an area is a range For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells ar.Value = ar.Value Next ar
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String Dim stopDate As String Dim startRow As Integer Dim stopRow As Integer startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)") If startDate = "" Then End stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)") If stopDate = "" Then End startDate = Format(startDate, "dd/mm/yyyy") stopDate = Format(stopDate, "dd/mm/yyyy") startRow = Worksheets("sheet1").Columns("A").Find(startDate, _ LookIn:=xlValues, lookat:=xlWhole).Row stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _ LookIn:=xlValues, lookat:=xlWhole).Row Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
The yellow highlighted column F (each Reason) in the sheet2 to be placed as main header in the next column and so on as seen in the sheet 3
If they have two similar item number with 2 different reasons - delete the duplicate item number and place the number in each column according to its reason as seen on row 16 on the sheet3
If they have two number with similar reason - just sum up the number
in a macro i have this: col=cell.column 'suppose col=16384. then i need to place in a cell this formula =(XFD2708*100/XFD2597)-100 so i'm looking for "something" that can convert 16384 in XFD.
on the syntax of things in VBA. here's a description of what i'm trying to do:
1) loop through a particular column ("N"), and compare the values (if not blank) with corresponding cells in another column ("V"). i.e. - comparing N1 to V1, N2 to V2, etc...
2) run If, ElseIf statements to display appropriate values in different column ("O"), but on the row that corresponds to the row the values in Step 1 are compared. i.e. - if N1 > V1 then cell O1 = value; if N2 > V2 then cell O2 = value; etc...
here's an example (but obviously not in proper syntax). hopefully someone can help me convert it to Excel VBA:
Function CalculateFR()
Dim Col1 As Column = ColumnN Dim Col2 As Column = ColumnV Dim Col3 As Column = ColumnO Dim Cell As Cell Dim IndexValue As Integer
For Each Cell In Col1 If Col1 > Col 2 Then IndexValue = 5 Else If Col1 < Col2 Then IndexValue = 4 End If Next Cell
I am trying to convert a row with cells referring to other cells (or with formulas in them) into columns, but paste special - transpose does not do the trick; the reference somehow changes when I try to paste it in, say A5 in the example file. I have excel for mac.
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
i am looking to convert data in columns to rows without using transpose but by formula. and i can drag it across to the right to keep getting new data.
i see posts mention about using functions like index, match? or other functions to do it?
I have a two vba codes. The first one defines the currency while the second one uses that info in order to convert them accordingly. I would like to edit the script in order to create a new column after my selection and then insert in each cell the proper currency as words (i.e $ -> USD, € -> EURO). So, if I select A1:A2 then only the cells B1:B2 must be converted into words. As an example:....