i have converted my text from notepad into excel format and i realise that some of my columns are shift 1 cell away for example my applications name should be in cell A but after converting, some of my cell A is move into Cell B. is there anyway that i can delete the cell that are blank and shift to Cell A or shift everything from cell b into cell a if cell a is blank? i have alot of documents to change..
View 10 RepliesI have the following equation programed in Excel, (see file attached). I do not understand while the match funstion is not working the lookup value is the on in cell N1 the array is from B1:F1. The return value should be 3 but I get #NA instead at the begining I was thinking that the values were different but then I compared cell =D1=N1 and the result was TRUE, meaning that they are equal
View 4 Replies View RelatedI need to transpose or move some of the data on Column B to horizontal position, is there a formula that I can use to do this? Attached is sample of what I want to do.
View 14 Replies View RelatedThe macro was orininally set up for the 7 days of the week. Now I want to set it up for the 31 days of a month. It's set to pull information from a register sales journal. Here is the button macro-
Sub Button2_Click()
Dim sDay As String
Dim sShift As String
sDay = InputBox("Which Day? (1 For MONDAY, 2 For TUESDAY, ... 7 For SUNDAY)")
Select Case sDay
Case "1"
sDay = "1"
Case "2"
sDay = "2"
Case "3"
sDay = "3"
Case "4" ........................
I'm just trying to copy certain cells from one worksheet & add them to the next available row on the next sheet, my data is going right down to the end of the sheet though (row 1048554) & instead of placing them on the same row its placing them in the row underneath the previous data in the next column. My code is below & I've included a table at the very bottom of how the data is coming out.
Sub Copydata()
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Sheets("PO").Select
[Code]....
I have this code:
Code:
If .Range("K" & t) = "AUTO" Then
.Range("K" & t) = Format(Application.VLookup(.Range("A" & t), rcore, 54, False), "h:mmA/P")
With .Range("K" & t)[code]....
It is intended to change the font colour to white when the cell = "AUTO". It does, but it also changes the contents of the cell (like NA, and NR) to white when they aren't equal to AUTO.
why this code only works till the first assignment?:
Dim pos As String
Sheets("Sheet1").Select
pos = Range("A1"). Find("John").Address
pos = Range("A1").Find("Mary").Address
pos = Range("A1").Find("Peter").Address
In the second assigment
pos = Range("A1").Find("Mary").Address
I get a "Object Variable or with block variable not set" error message.
I want to paste a group of non-contiguous cells, and paste the values elsewhere on the sheet while maintaining the same relative position. is this possible via macro somehow?
for example, in column A i want to copy A1,A2,A5,A9; and paste into D1,D2,D5,D9 in one step.
I've got a list of story titles that are organized by genre, and I'd ultimately like to have a formula (not VBA) that automatically generates a "story id" based on the title's position within the genre (not the list overall). Here's a snippet of the list:
CREATE TABLES LIKE BELOW?IDGenreTitle
1.1GeneralTitle 1
1.2GeneralTitle 2
2.1Horror / SupernaturalTitle 3
2.2Horror / SupernaturalTitle 4
3.1Sci-FiTitle 5
3.2Sci-FiTitle 6
So what I'm looking for here is the formula that would give me the ID shown in the first column (I can modify it later for specific purposes). I've tried variations on the MATCH, INDEX, ROW, and VLOOKUP functions but just can't seem to get it quite right. Note: All the items in the Title column are unique - there are no duplicates.
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedI am having trouble converting time cells into numbers.
The cell I'm looking at visually shows "135:42:00". If you look at the formula bar, it reads "1/5/1900 3:42:00PM"
How do I start a series of conversions to get an adjacent new cell that would read 135.7?
I have a spreadsheet that has 3 different dates in 3 different cells:
B4 - 3/24/2014
B5 - 4/6/2014
B6 - 4/14/2014
From that information I need once cell to display "Mar 24 - Apr 6 paid Apr 14"
Reason being is that I am importing journal entries into an accounting program and I am allowed one cell each row for the description. This would let me change the dates only and my descriptions will fill out before import. It is important that the months are abbreviated to 3 letters as I am limited on how many characters the description can be.
I have rxd a pdf file that I need to manipulate I have copied the data into excel and now need it in individual cells:? The data looks like this: 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03. this is:
phone number calling | date called | time called | number called | duration in minutes | cost of call. If you're out there I'd be more than happy to hear from you. the cells to cut the data into will be formatted to take the data in the correct form. 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03
phone number calling | date called | time called | number called | duration in minutes | cost of call.
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!
Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range)
Dim Roundname As String
Dim Result As Double
Dim NameColRound As Integer
Dim ListObject As Variant
Roundname = "Round " & Round
NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0)
For Each ListObject In NameList
If ListObject.Value = Name Then
Result = Result
ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then
Result = Result + 1
End If
Next
maxbarometer = Result
End Function
when i try to get the final row witht the following:
KROGrow = Cells(65536, 1).End(xlUp).Row
i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
View 2 Replies View RelatedWhen copying data from a website to excel, excel recognizes the dates wrongly.
About half of the dates he sees as: DD/MM/YYYY, while it should be: nothing/MM/DD.
For example: The cell contains the date: 01/04/2029 (The first of April, 2029)
While it should actually be: 04/29 (The 29th of April, no year!)
For all dates it starts with the 1, followed by a month, followed by a year (which consists of the year 2000 + the actual day), which is totally wrong.
About the other half of the cells, it just contains text with the month and the day (which are correct), but excel doesn't recognize this as a day.
For example: The cell contains the text: June 12
When I set the cell format to date (or any other format for that matter), nothing happens, so excel does not see this as a date.
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies View RelatedI have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution.
I'm using excel 2003.
Let me try to explain my problem with a simple exampel.
I have a column with numbers and one with formulas pointing at these.
A B
2 =A1
4 =A2
3 =A3
Now I want to sort column A, and the column B should keep pointing at the original number. So I want this:
A B
2 =A1
3 =A3
4 =A2
But I get this:
A B
2 =A1
3 =A2
4 =A3
This output is what I would expect if I used absolut reference($).
Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.
I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.
My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.
I have modified this micro I found in a forum. All I changed was the Range on Sheet 1.
[Code].....
It works fine except when it cuts to sheet 2 it starts at on column B I need it to start with Column C. What do I need to add ?
Attached File : Survey.xlsm‎
I have the following code which saves my worksheet with a name extracted from particular cells:
Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName
End Sub
This (for reasons I don't understand) saves the file to My Documents by default.
What I would like it to do is save to another folder within my documents as default.
how to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.
View 2 Replies View RelatedI have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in
english?
=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))
Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time.
In the example in the sheet the load time is 01:00
The start time is 23:00
The end time is 23:35
Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late.
Does anyone have a formula to slove this issue
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code:
Sub reviews()
Dim cell As Range
For Each cell In Range("L1:L200")
If IsDate(cell) Then
cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value)
cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value)
cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value)
cell.Resize(, 10).AutoFill cell.Resize(, 11)
End If
Next cell
End Sub
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1
But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?
View 9 Replies View RelatedI'm trying to parse a text file into Excel and the date is coming out as "1940" instead of "2040". In the text file the date reads "5/14/40". I've already changed my regional setting in Excel so that if I type in "5/14/40" it is understood to be "5/14/2040". For some reason this doesn't work when I parse the data though.
View 9 Replies View Related