The following bit of code has worked for me but when it goes back for the next b it still finds the address as $j$13. This address does match the criteria I want but what I want the programme to do is move to the next address matching the criteria in the range...
View 4 RepliesI have this loop, I get a range of specific cells and then I loop over this collection searching for a value, if this value exist I wanted to move one place to the right and then set a value on that cell, but for some reason when I put this offser(0,1) parameter, the loop became infinite, not sure why the code is doing this:
Code:
For Each c In Worksheets("Parameters").Range("A3:AR3").Cells
adress_start = ""
adress_letter = ""
[Code].....
I am trying to create a For Loop where the RND from array column sptemp(i,2) is static throughout the entire loop when I pull the data out separately by columns to create a chart. The data works perfectly if I print it out on the excel spreadsheet via ctrl+shift+enter as an entire array but I am trying to graph the columns and the RND is updating and not syncing with the same random number, as sptemp(i,3) and sptemp(i,5) are both linked to sptemp(i,2) which is the RND. I am trying to graph column 3-5 on the Y-axis and column 1 on the X-axis, and column 3 and 5 should look identical after subtracting column 4.
View 1 Replies View RelatedLastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To LastRow
Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _
((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then
Worksheets("Sheet1").Range("AE" & i).Font.Color = 255
End If
Next i
My loop here works just fine, and my Formula works in testing. However when I move my formula inside the loop I get an error.
Is the syntax wrong in this line = Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
I want to use the function VLOOKUP at my VBA program inside a FOR Loop
View 3 Replies View RelatedI'm working within a nested FOR loop. If you notice when I'm Assigning "ECD" I'm trying to average the cells from J (which is row 1) and column 11 to column 12. It keeps on telling me "Application-Defined or object-defined error".
Code:
Sub copytab()
Dim WSName As String
Dim NumbSheets As Integer
Dim NextRow As Integer
Dim i As Integer
[Code] .......
I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.
I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004
I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.
'Daily Entry Sheet'!$B$8:$B$138 = Date
'Daily Entry Sheet'!$F$8:$F$138 = Property #
'Daily Entry Sheet'!$G$8:$G$138 = Property Location
'Daily Entry Sheet'!$H$8:$H$138 = Work Order Number
Following is what I presently have coded.
The following Works but is not completely as I need.
=IF(OR($F8=0,$F8="",ISNA(G8),$H7="Work Order #"),"",IF(ISERROR(INDEX($H7:H$8,MATCH($F8,Prop.,0))),"",INDEX($H7:H$8,MATCH($F8,Prop.,0))))
This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.
I looked for a function similar to the sumif or countif
=SUMIF(Prop.,$F4,WorkOrdNum)
=SUMIF(Date,"="&$V6,Total_____Hours)
But I don't need to add the work order numbers.
Countif can give me how many instances property # 919 is used,
But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.
I have got a loop which is working fine but now i need another loop which will run till the end but need to repeat itself as soon the column x become 1 the highest number would be 3
here is my main loop A1 = 5000
and second loop need to run inside the this loop
Sheet1.Activate
i = Range("A1")
For b = 1 To i
If Cells(1 + b, 3).Value = "P" Then
Cells(1 + b, 29).Value = 1
Else
If Cells(1 + b, 3).Value = "S" Then
Cells(1 + b, 29).Value = 2
Else
If Cells(1 + b, 3).Value = "C" Then
Cells(1 + b, 29).Value = 3
End If
End If
End If
Next b
I am trying to make a macro that looks through range "R1". If it finds the value "X", it copies the 3 cells that are offset 17 cells to the left of it (cell A, B, C) to a different worksheep. I've been working on it, but can't seem to get it going. This is what I got:
View 5 Replies View RelatedI have a data sheet with employee information. Only one column. It prefixes information with codes, but keeps it in the same cell. I want to move data of certain types into their own columns, but the amount of data is variable, so I cannot simply move every Nth cell, etc.
i.e.
Employee#1
200 Firstname Lastname
204 99999999 (Employee ID)
G38 00005000 (i.e. Pension Deduction)
H38 00007580 (i.e. Benefits Deduction)
X96 00012099 (i.e. Staff Club Deduction)
Employee#2
200 Firstname Lastname
204 99999998
G38 00000775
X96 00001000
So you see some employees may have different codes altogether. But I know that I want all the cells that start with 200 to be offset (-1,1), and all the cells that start with 204 to be offset (-1,2), and so on so that basically I end up with columns of info instead of a one column list.
I have been reading and studying other peoples' macros, and am just starting to grasp the basic. When I wrote my own to accomplish this, I put this together, which doesn't work. But I don't know enough to know what I don't know.
I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.
Here is my code:
Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long
f = 0
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To lastrow)
[Code] .....
Let's say sheet - "delivery" - has a row1 that includes all possible delivery days. So A1 is 1.9.2013, B1 is 15.9.2013, C1 is 1.10.2013 and so on.
I want to make a macro, that will gradually go through whole row 3 in sheet "delivery" and look for "A1" date in sheet "Orders". Sheet "orders" have for example in column B the date of delivery, and in column C product of the delivery.
I want the macro to find all deliveries with "A1" date, and paste all products that will be delivered on this day under cell A1 (sheet delivery). then move on to the cell B1 (sheet delivery) - find all orders in sheet orders, that will be delivered on B1 date, and list all products with this delivery date under cell B1 (sheet delivery).
Example:
Sheet order
A
B
C
Date of delivery
Product
1.9.2013
orange
[Code] ........
Unfortunately simple Pivot table is not able to do this simple list.
Search Column A for a name (using an array if possible). If it finds this name, add a string of text to the AA cell on the same row that it found the name on.
For example,
If in A1 it finds the specified name. It then adds a string of text to AA1.
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
I want to get the text inside the cell which is to the left of the first blank cell of a column.
I show the problema in the attach imageSin tÃtulo.jpg
I have this: =LOOKUP(Budget!J3,B11:B42,E11:E42) RESULT: 5%. what I want: =LOOKUP(Budget!J3,B11:B42,(X3)11:E42) RESULT: 5%. I want excel GET the value inside X3 and and go to the other sheet with this variable. X3 = E
View 2 Replies View RelatedI'm trying to get dropdown in cell. Depending on what is chosen, I then want another dropdown in the same cell of either New or Old for the item previously chosen. Hopefully the example helps because even I'm confused by this.
View 6 Replies View RelatedI am wondering if you can write me a VBA code for comparing the value inside a cell.
Code:
For i = 1 To 159
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) then
..............
Else
[Code]....
is it possible inside this link to put a variable reference? for example to have a cell in my excel which in this case would be Report FY09 and if i change it to Repot FY08 the link would be c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4 or in another case if i have the name of the sheet like
='Report FY09'!D4
to do it
='Report FY08'!D4
or even the excel file
c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4
to do it
c:Report FY08[PPV REVENUES FY 08 - Actual.xls]PPV Actual'!$C$4:$BB$4
Each cell below contain a number and a time + ET. if like say i want to delete the time + ET and leave only the number inside all cell.
1.1425 8:20pm ET ON A1
3.0805 6:10pm ET ON A2
814.30 6:42am ET ON A3
798.70 11:27am ET ON A4
380.00 9:10pm ET ON A5
After formating I need to multiply by 2 . As you can see my Cells.Value = Selection.Value * 2 is not correct. How can I accomplish this?
With Range("I17:I4000", Range("I" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=ReplaceChar(I17)"
.Value = .Value
Selection.NumberFormat = "0.00"
Cells.Value = Selection.Value * 2
End With
I'm starting a project where Excel will be used as the main UI for defining a table of data. I'm expecting to define a "template" - xlt i guess - that users can open and save as an xls, without over-writing the template. Users will populate the spreadsheet with a lot of help from user defined functions. I'm just getting started and would like to populate a particular cell with the current date, but only the first time the sheet is opened. I tried checking whether a cell was empty before assigning a value to it, of course this meant recursion!
Also: It might be nice to use the Today() function inside a UDF, but TODAY isn't a member of Application.WorksheetFunction - is there no way to reference TODAY() from within a UDF?
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
I am attempting to setup a spreadsheet that summarizes data from a set of other workbooks. The woorkbooks the data comes from is different for each year. However, I would like to have just one summary that can be changed based on the year entered.
In B1 the user enters the year. Say 2012
In the bulk of the spread sheet below the cells refrence
='C:...["&B1&" Pt. Visit Stats.xlsx]Summary'!$B$6
As I am sure many of you can guess I am getting a #REF! error. I just do not see why it shouldn't work.
I do not want to use the indirect function, because the refering workbooks would need to be open to draw data. I am guessing that I will probably need to research the Harland Grove Pull VBA work or more likely include a copy of Morfunc and then use the indirect function. But, I though I would ask here first just in case I am missing something relatively simple.
I want to place a thumbnail image inside a cell for each row of my spreadsheet and have those images STAY in that cell. Once I get the images in the cell, they float - if I sort the rows, the images get all mixed up.
View 9 Replies View RelatedI'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments)
If conditions Then
nameOfMyFunction = "No GRP"
ActiveCell.Font.Color = RGB(255,0,0)
End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
rearragning text on excel. I believe Macro will be needed to solve this problem so if anyone can help me out, it will be a big help. Thank you.
Example 1:
I need to rearrange
"trace Silt, cm SAND, some- f Gravel" into
"cm SAND, some- f Gravel, trace Silt"
Example 2:
I need to rearrange
"some+ Silt, f SAND, trace- f Gravel" into
"f SAND, some+ Silt, trace- f Gravel"
There are many different cases for this problem. So if there is a way for the computer to recognize the Capitalized word and move it to the front and rest should be arange in order listed below.
Order
and+
and
and-
some+
some
some-
little+
little
little-
trace+
trace
trace-
each term is seprarated by commas.
For example
f SAND, some+ Silt, trace- f Gravel
each color represent one complete sentence. and the order for them should be capitalized funciton first, then the follow the order provided above.
How can I view hidden characters inside an excel cell. I have an excel file that I receive from our vendors. After verifying the data, I save the file as a tab delimited .txt format. When I open the .txt file I see some data with " " at both ends. i.e. "800 North Ave. Suite A". The thing is I don't see the " " in excel. This tells me that these are non-printing characters.
View 4 Replies View RelatedI have 4 cells with simple data in them. In another cell, I would like to make a phrase and include all the data from the 4 cells in that phrase.
Example:
="Jackpot: &G2 (&G1) / &G3 = &G4 each"
I already tried this:
="Jackpot: "&G2" ("&G1") / "&G3" = "&G4" each"
Am I missing something? I'm pretty sure this is doable, I just don't know what's missing.
i have a data sheet and i am trying to store the row value of a user input time. for the most part my code works but for certain times it returns a 0. it seems like times which end with a 3 are not returning any value.
my code looks kind of like this:
Dim StartA As Date
StartA = InputBox("First start time (ex: 2:30pm)")
For i = 1 To 9000
[Code]....