i created list in Excel and i wrote into (a1 cell-income, b1 cell-expence, c1 cell-kredit and d1 cell-debet). i enter a2 cell income (for ex:1000) and b2 cell expence (for ex: 100) and I give condition in VBA that if income greater than expence then VBA writes a2.value-b2.value into d2 cell else if expence greater than income then it writes b2.value-a2.value into c2 cell but i only define this one row (for ex: c2, a2. b2. d2).
i would like to assign it the rows as long as i want
that is i would like to create loop
(for ex: a(i), b(i) and so on
The goal of that formula was to return a Date/Time stamp that is fixed and doesn't change over time. The formula is intended to run in which ever cell that I select to enter it into. I named the formula:
=DS
What I am trying to do is create a macro that will run the formula using a hot key function. I want to be able to select a cell and hit CTRL D and have that above formula run in that cell. I have multiple sheets in my workbook and I need the macro to be able to run on any sheet in my workbook. Below is what I have:
Sub DateStamp() ' ' DateStamp Macro
[Code]....
I was trying to apply the macro to only a certain range of cells on any given sheet.
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):
My goal is a macro that exports a .txt file where the user selects desired rows (strains, not necessarily contiguous), header columns (collection data), and a single sequence column. The output format (called fasta) is:
I am trying to run for_each_loop to in a module run an activity on other excel file.
Other excel file is having 8 sheets and loop is working fine till 4th. but when loop goes to 5th sheet it says Application defined or object defined error. I am confused because i have defined the objestSh as worksheet in the beginning.
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
Ive created the following code which I want to use to create a name in excel but it doesnt work.
when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"
How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.
Function Allocation(LOB As Integer, CostCenter as Integer) As String If LOB = 00 And CostCenter <> 8300 Then Allocation = "Yes" ElseIf LOB = 1, 2, 3, 4, 5 Then Allocation = "No"
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
I want to create a user defined function to search a column of data for a part number.
If it exists I want to have a the UDf returna "fail" otherwise "pass"
Here is the code I was trying to use
Function firstpass(SN As String) As String ws = Worksheets("Defects") c = "" With ws.Range("a1:a9999") Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then firstpass = "Pass" Else firstpass = "Fail" End If
End Function
This function only returns a "#value" and I don't quite know how to troubleshoot it.
I am looking for a Macro that will allow me to update the range of a Defined name range such as every time I add a name into the list, the only thing I will be doing is passing the client name and the list of client will be automatically updated and my drop down ListBox will show the new client name, which can be selected by the user.
I have an excel sheet that will have a column with out of sequence numbers. I need a script that will evaluate each row and insert a row and the missing sequence. Below is the code I have written but I keep getting a compile error saying Else without If.
I'm sure this is easy but I'm trying to create a for...next loop that goes across the row. My final output will be basically be to see if the cell says Saturday or Sunday and offset by 2 rows and highlight the rest of the column.
Code:
Range("A1").Select For X = 2 To Range(Activecell, Selection.End(xlRight)).Column Range(2, X).Select ActiveCell.Value = "Column" Next X
That's what I came up with but it doesn't seem to be right.
Highlighted in red near the top is cell a1, this needs to change to a2 then a3 then a4 and so on for as many times as i need it to.
highlighted in blue near to the end of the code is cell a1,this needs to change to a41 then a81 then a121 then a161 and so on(so +40)for as many times as i need it to.
I want to loop through a list of numbers and add a hyperlink to each number. This hyperlink refer to a sheet with the same name as the number. how I shall use hyperlink line below. The loop and everything else is ok. I use a loop like the one below and want to refer the Hyperlink to activecell each time
I have been trying to create new worksheets, name them and create webquery according to the input on table B4:C13 in test1 sheet attached (code is in module 1). After I run the code it stops after creating sheet with name 1 and shows error 1004. here it is the
I've got a data set (words and text) where in column J the number 100 appears at random intervals. Each time the number 100appears, i want to take all data from cell A:J and copy that data to a new worksheet and then name it with the word in cell A.
I am trying to do payslips. Basically I have one worksheet with about 7 columns. I have a 2nd worksheet that is like a template payslip.Worksheet 1 has hours worked and pay etc.
I want to find a method to loop through worksheet 1 and using worksheet 2 as a template create more worksheets and have the values come from worksheet 1 in the new worksheets.
I keep getting errors with this vba code. I'm trying to make 10 new worksheets with the name of each worksheet in the Newname array. I'm trying to put the same formula (with the difference that each formula would take from a different "Newname" file. I'm trying to put first formula from A1 to A 560 and second formula from B1 to B 560.
Sub Namesheetformula() For x = 1 to 10 Newname = Choose(x,"A","B","c","d","e","f","g","h","i","j") Firstformula = if(isna(match('Master!'A1,'Newname'!A1:A560,0),"",'Master'!A1)
