I have got mentioned below result after running a macro and I want mentioned below formula to be put after counting rows, because each time as per my query when I run macro result is different in rows as you can see in below table the formula I put in column F in rows 19, 20, 21, and 22.

I'm trying to learn VBA programming (I started some years ago already, but felt it was a bit too difficult for me) and I would like to ask two (perhaps very basic) questions:

1 - Can someone give me a code which does the following : In A1 I have a string : "This is a test." I want it in rewritten in column C1-4 as follows : C1 This C2 is C3 a C4 test.

2 - A code which does the following : In A1 I have a word : "Testing" I want it this word as split up in 1 character/cell as follows : C1 T C2 e C3 s C4 t C5 i C6 n C7 g

I have a userform containing upto 30 lines of data, each line being four fields.

The four fields are called txtMAWBNo#, cbDestination#, txtGW# and txtCBM# where # represents the line number so 1st line would be txtMAWBNo1, cbDestination1, txtGW1 and txtCBM1 to line 30 being txtMAWBNo30, cbDestination30, txtGW30 and txtCBM30.

The number of lines the users are allowed to enter is controlled by a variable called MAWBNoVar, which has a min. value of 1 and a max. value of 30.

The data needs to go to the next available line in the sheet, which can be determined by using xlup + 1.

Assuming the next available row is #244 and that MAWBNoVar has a value of 4 then what i need to do is this.

I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?

I have company asset data listed by item on wksht 1. Example:

Company A Computer 200 Company C Coffee Machine 21 Company A Car A123 Company B Computer 70 Company A Chair B14 Company B Desk C56 Company C Computer 59 Company C Desk C789

I want to sort and covert the column data on wksht1 to row data on wksht2. Example:

Company A Computer 200, Car A123, Chair B14 Company B Computer 70, Desk C56 Company C Computer 59, Coffee Machine 21, Desk C789

I'd like to do this via VBA/Macro(s) if possible.

I have to do this every month, so it would be nice to append the existing data to an "archive" file on wksht3 and then create this months data.

The data is imported each month via a text file with comma delimited fields which become the column data in the top example.

I basicaly need to look down column Q8 to Q52 for the falue "C19" If that value is present - Then I need to look up the value of associated with what is in its respective cell in column O8 to O52. Then add them all together.

I have the formula that follows and it works, however it is huge. I have to repeat the IF,Vlookup 44 times. Just wondering if there is a better way.

Here is the formula I am using.

=SUM((IF(Q8="C19",(VLOOKUP(O8,clusterequipmentvalues,2,FALSE)),0)), (IF(Q9="C19",(VLOOKUP(O9,clusterequipmentvalues,2,FALSE)),0)), (IF(Q10="C19",(VLOOKUP(O10,clusterequipmentvalues,2,FALSE)),0)), (IF(Q11="C19",(VLOOKUP(O11,clusterequipmentvalues,2,FALSE)),0)), (IF(Q12="C19",(VLOOKUP(O12,clusterequipmentvalues,2,FALSE)),0)), .... all the way to C52.

need to write this forwula for excel 1000((value from cell)+.256)=(((x+.314)1000)+9)1.0625. I would like to plug in a value from a cell and find the comparative value x. I am comparing the airfregiht cost between two carriers that have different surcharges for fuel, secuirty, and tax. One has no security or tax.

I am trying to write a formula using IF, OR & AND combined together, but I can't see to get my formula in the correct order. I have tried moving several sections around but I still keep getting an error.

I'm looking to write a nested IF formula which will be dependant on certain cells that have errors in them (because those error cells have a formula in them which can't formulate becuase the numbers needed for that formula haven't been entered yet).

For example.

Cell A1=#DIV/0!. Cell B1=5.

In cell C1 I want to calculate the average between A1 & B1, but there is an error in A1.

I'm trying to figure out how to make a formula which ignores an error and gives me the value in the cell which doesn't have an error. But I also need it to calculate the average if both cells have a value in it.

I have a sum field in excel which contains the formula to compute sum of certain cells. For eg. Cell C1 has value (C2+C4).

I have written a vba code to find cells whose value are to be included in the sum field (variables Lrow1,Lrow2 and Lcolumn1,Lcolumn2 contains the row and column number respectively). These cells may differ each time vba code runs. Can I write formula in the sum field based on Lrow and Lcolumn variables.

I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls

I thought that a nested if is what I needed, but now I don't know what I need I have explained in greater detail within my sample that's attached. Quote:....

I don't know how to write a function for what I need. Right now I have a function that says '= if $b7=0,0,if($d7>$d5,-1,1)), but I've found a problem. If there's more than one zero it throws off what I'm doing. I need a function that will ignore any zeros within column D and use the second to last number. For example:

The formula in E12 should read '=if($B12=0,0,if($D12>$D8,-1,1)) The formula in E13 should read '=if($b13=0,0,if($D13>$D9,-1,1)) The formula in E20 should read '=if($b20=0,0 ,if($d20>$d12,-1,1))

I just don't know how to write this so that it will go back to the second to last number other than zero.

I have a spreadsheet. In this sheet the data is added daily so one column is increased everyday. The rows may also be increased. What I am trying to do is to sum the data in each row for a range of first cell in the row to the last non empty cell in that row and this has to be done for all the rows. So I thought the for loop would be useful for such requirement and I tried to write the following code. But unable to write the sum formula in the last empty cell in a row and I get the value error. The code is as follows.....

Sub sum_on_LastEmptyCell() 'find the last empty row in column A lrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1 'find the last empty column in a row

[Code] ........

Though one of my friend told that it can be done with "with and end with block but I am not aware of with and end with block.

It would be better if you tell me that how can I put the sum formula in my code. How can I use variables which return the row number and the column number in the sum range within the For loop because I want to put If Else condition for the calculations within the For loop i.e. if certain condition is true then I want this calculation to take place otherwise do something else. Moreover it will be easier for me to understand. Because I can use different formula based on different cells as well. Is there any way to do this?

I've got a "Date" Column which has every day of every month, and next to that several columns with different types of data to correspond to a particular date.

To simplify this (because there are TONS of dates), I've made another "Date" column that displays only every 5th day. Now what I'd like to do of course is average the corresponding data over five days.

I don't know how to write a single formula that I can copy down that will do this.

I've attached a sample spreadsheet with only one month's data, the stuff I need a formula / code for is in red

I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.

The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.

I am extracting data from an electronic medical record (EMR) which has many search limitations. I am able to export data into a CSV file (attached) to perform some calculations (All the patient data is fake btw). I am trying to find the number of appointments for each patient within a specific date parameter. For each patient, one appointment appears on one row.

pt appt sample.png

What I want to do is count the number of rows (appointments) for each patient in an efficient manner. Right now I've just used the COUNTA function, but don't want to repeat this for every patient, due to the way that the workbook is set up. The data is all from the CSV file from the EMR; the only column I've added is the "Appointments since 2013" column.

The patient data is fake because I am using a test server for the EMR. However out in a real doctor's office, there will be over 2000 patients with possibly up to about 100+ appointments.

Not sure if there's formula out there that would make my task a lot easier and more efficient, or I need to play around with the columns to set it up differently.

I need to insert a formula to count only the visible rows in a database. I currently have =row()-6 etc. but when I hide certain rows they are included in the count. What formula can I use to ignore the hidden rows and only show the live rows?