I have the following VBA code and try to sum the range from B2 to CY2 for DA2; then B3 to CY3 for DA3, etc. Therefore I use the for loop. It results in "Application defined or Object defined error".
View 4 Replies
I have a variable array, that is, the first cell of the array is variable and the last cell is variable. I have dimmed the first cell , "firstcell" as a range. I have dimmed the last cell , "lastcell" as a range. I'd like to sort the array but first I have to select all cells in the array. Need the proper syntax to select all cells between "firstcell" and "lastcell" in my macro.
View 2 Replies View RelatedI need syntax using SUMPRODUCT. I have these two lines of code
View 2 Replies View RelatedI'm not sure where my syntax is wrong, and I can't think of what to google to learn more about the possible options I have when creating and declaring the value of variables.
I am simply trying to define the variable aWB as a workbook, whose name is found in cell B6 on the worksheet named "Start" in the workbook named "DW1". This seems to make sense to me, but I am not typing it correctly.
Code:
Dim aWB As Workbook
Set aWB = Workbooks("DW1.xlsm").Worksheets("Start").Cells("B6").Value
I have the following code, which executes just fine:
[Code].....
The problem is that I need to run this again in a different column, and the data is held in another sheet that I have given the variable StepTwo to.
I'm not sure of the syntax to do this. Basically - this is what I need the code to do:
[Code] ....
Understandably this is totally incorrect, but it's the best I can do to explain what I'm actually trying to do....
The two parts of SUMIF are both held in a workbook that I've given the variable "StepTwo" to. Both sets of data are also held on Sheet(1) of that workbook.
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta()
Set wba = ActiveWorkbook
On Error Resume Next
If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then
Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt"
Else
Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls"
End If
Set wb = Workbooks("Projekt.xls")
wb.Activate
If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then
MsgBox "overall doesn't exist!"
Else........................................
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.
I need to writing a VBA code for each of above (integrating text & cells, saving results as text)
I am preparing a daily report available in sheet1 with the actual input in sheet2 generated through a tool.
The report contain three columns i.e. total cases, Total correction done and Not corrected. From the total cases on a specific date, I need to calculate how many correction have been done and how many are pending.
E.g. 01 January 2013=5 cases are uploaded in the tool.
on 02 January 2013, it was observed that 3 cases were corrected and 2 are pending But 9 cases cases are uploaded on the same day in the tool .
So Total correction done (C3)=3
Not corrected(D3)= 11
I have different dates in column A, let's say:
10/26/12
11/1/12
11/15/12
2/12/13
I want to say that if the date is <= 12/31/12, put "LEASED IN 12", othwerwise put "LEASED IN 13".
My formula:
=IF($A1<="12/31/12",'LEASED IN 12","LEASED IN 13")
I get only 'LEASED IN 12".
It show error, how to correct it .
[Code] ......
=COUNTIFS($K$54:$K$84,">25
View 9 Replies View RelatedSub startrd3_click()
Range("B5:AY64").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Key2:=Range("C5") _
, Order2:=xlDescending, Key3:=Range("E5"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' ERROR CHECKING HAPPENS HERE TO MAKE SURE PEOPLE HAVE NOT PLAYED EACH OTHER BEFORE
Columns("J:W").Select
Selection.EntireColumn.Hidden = True
Range("I4").Select
End Sub
If any of you gamers out there are familiar with the Swiss Pairing System, the goal of this sheet is to perform the pairing functions based on results. The goal of swiss pairing is to partner folks up based on win loss records, however, the players can never play each othe more than once. What i am wondering is how to create the vb code required to make sure the pairings are unique while keeping the rules of swiss pairing together.
Here's how it works:.............
In A1 have two words, say "first" and "second" as two lines entered by pressing Alt+Enter after typing the first word. The requirement is remove this formating OR to get these two words separately in single cells. ie B1=First & C1=second.
View 3 Replies View RelatedI am trying to enter the last name "Teh" in a cell. How do I turn off auto spell correction that turns "Teh" into "The"?
View 3 Replies View RelatedI need to step out of a macro based on a user respone, and I don't know how to end this macro if the user needs to correct a cell entry.
If they respond "no", I want to sub to end and select the cell where the correction needs to be made.
I need a hand with a worksheet that we use.
We do a penetration test on soil, write down the values from the machine, then enter into excel and it plots a line graph.
Depending on the values, sometimes a correction is required. This is manually done at the moment.
I would like to have excel do it for us.
I have attached an example of a manual correction I have done as well as the excel calculation worksheet.
There are 2 results given (one at 2.5mm penetration & one at 5.0mm penetration), we calculate both, then use the highest result for the report.
the x value is a constant and the y is a variable.
Attached Files :
Copy of Master WA CBR Worksheet Soaked.xls‎
DOC230714-002.pdf‎
I'm trying to calculate the sum of rank vlaues in an array formula (required for a Mann-Whitney U-test calc). For example, I have the results of a survey quesiton (1-5 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties.
The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below.
$C$% = "Sales" and $F$5 = "Marketing"...
={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}
I have this Formula in a excel sheet =IF(AND(COUNT(F12;G12)=2;G12<F12);G12-F12;IF(AND(COUNT(F12;G12)=2;G12>F12);ABS(F12-G12);""))
My problem is that when the result is 0 it just leaves a blank cell without a 0.
Can i correct this so that when the result is 0 it will actually show a 0 in the cell? I can´t put a 0 in the end of the formula because then it shows zeroes in all the cells without information aswell.
I have a date of the "Sep-07"in a1 in a2 i have "fred" and in a3 "expenses" when i try and combine them so that i can save the file name as a variable ie sept-07 fred expenses. I get
39336fredexpenses
How do I get the date not to read numbers?
I want to combine two cells and add text. Then delete column D.
View 4 Replies View RelatedI want the user to select a currency pair in cell B4, eg, EUR/USD
Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....
=MT4|BID! & cell B4 & "m"
the cell b4 is the bit i am not sure about.
am trying to code a loop but I have no idea where to start.
With the help of you guys I have already got some code from a previous thread which gives me a list of hierarchical data now I want to be able to loop through this and actually have the 'parent' next to it.
For example:
1 Shops
2 Butchers
3 Meat
2 Bakery
3 Bread
I would like to be able to say
1 Shops
2 Butchers Shops
3 Meat Butchers
2 Bakery Shops
3 Bread Bakery
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
I'm trying to write a statement something like: B1 = data. B2=IF(B1/8=0,"",IF(B1/8=INTEGER,B1/8 & "8/8",B1/8+1)). This is meant to identify which relay on a relay board is associated with the given data. For example if data=3, B2 = 1 3/8 meaning the 3rd relay on board 1. If data = 77, B2 = 10 5/8 meaning the 5th relay on board 10. I could do without the 10, but the 5th relay on that board is important to me. In summary, I think my trouble is identifying when the product of B1/8 is an integer. I may also be having trouble combining value and text. Well, there it is. I may very well be going about it all wrong.
View 3 Replies View RelatedIn column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies View RelatedI have two columns each column has a combination of the same 3 words "cash", "card", and "trade". The are formatted as "Cash & Card & Trade" or "Cash & Card" or "Card" ect. I want a formual that can combine the entries written into each columan into one column. I will put an example below.
A
B
C
D
1
Client
new
old
both (formula in this column)
2
name 1
Cash & Card & Trade
Cash & Card
Cash & Card & Trade
3
name 2
card
cash
Cash & Card
[code]....
Column A has the following values:
au:asd
au:qwe
au:zxc
etc
I want to combine the text from each cell into a text string so that each cell's text is separated by a comma, thus:
au:asd,au:qwe,au:zxc,etc
I can do this with a formula,
=a1&","&a2&","&a3
but this may exceed the 255 character limit when the text string needs to be transferred to a query.
with a formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data. see examples below in Column A and the intended results in Column B.
Column A
row 1: Joe 1.234, Al 6.89656,
row 2: Sam 6.5,
Column B
row 1: Joe 1.23, Al 6.90
row 2: Sam 6.50
I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text. The cells are arranged like this:
A1="One"B1=Cell where the text is combined
A2="Two"
A3="Three"
A4="Four"
I set the cell alignment in B1 to wrap text and use the following formula in B1:
=IF(A1=””,””,A1)&IF(A2=””,””,CHAR(10)&A2)&IF(A3=””,””,CHAR(10)&A3)&IF(A4=””,””,CHAR(10)&A4)
The problem is if there isn’t anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text. Is there a way to change the formula so that it will do the following three things:
1. If the cell A1 doesn’t have anything in it then don’t put the value in B1.
2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.
