Answer Questions

• Kimball what is wrong with this formula? *9 Views & no one knows?*

  =IF((Weeks!D(B1+6))="",1,2)   whats the error with this D(B1+6) is ment to respresent a cell with D    = the value of B1+6   what am i getting wrong If This Helps The Cell Forumla is Going to Be used this way Sub NamesDropDown_Change() ' check for already inputted data For x = 1 To 7 Cells(x + 2, "K").Forumla = "=IF((Weeks!D(B1+6))="",1,2)" Next x End Sub But currently this formula is not working Hello Milkshake, What I was trying to say was, suppose you had these two bits of code: Code version 1 f Sheets("Weeks").Cells(B1Value + 6, i).Value = "" Then Sheets("Add Details").Cells(x + 2, "G"). Value = 1 ...Show All

• dan59 Link Table

  i have a link table in access which is linked to SQL Server, by opening the linked table itself, i could see the records etc, but if i try to access the link table through vb coding, it is saying that i dont have permission to SQLServer. How to overcome this issue. Many Thanks Syed ...Show All

• progames25 How to convert excel file to .DAT file?

  Hi I need urgently in my job to converfile to .DAT file Can some one help on the same. Thanks Kishore. Please elaborate on the .DAT file, ie format, which application will use it etc ChasAA Here here...I need to do the same thing. I have Excel files I've saved as .csv delimited files. The files are pipe-delimited but I don't have a pipe-delimit extension. I need to get these files saved with .dat extension. I am copying them to an FPT location where another person is grabbing. them. They need data files with .dat extensions. I believe their scripts looks for these automatically. I have not found yet a method to convert .xls to .dat. Thanks! EW' ...Show All

• thukralz Returning the Server Referral By DFS link

  I am trying to write a script that finds where a user is logged in but the script will not work as it looks at the profile path and then takes the serve rfrom there and looks at the LanManServer on that and queries the sessions for the username. This does not work as the Profile path is a DFS path for example: \\One.Server.local\data\Users When this is put into the script it just errors. I need to be able to convert the DFS path to the server the link points to. Please help ...Show All

• ACCOUNTINGONLINE.US Solved - Help with Excel VBA Code

  I am trying to format different spreadsheets. I need the data to be in the same cells for each worksheet. This data is in column A. I need to check to see if DBCS# ( ) is above Tour, and if so insert rows until Tour is in row 175, column A. The problem is that the number after DBCS# is never the same. How can I use an IF statement to make sure that the cell above Tour says DBCS# (whatever the number is) This is the If statement that I tried. If I put DBCS#46 in my IF statement, it will not work because the number after # isn't always 46. If (Range("A174:A174") = "DBCS#") And (Range("A175:A175") = "TOUR") Then GoTo label_177: Else: Do While (Range("A174:A174") <> "DBCS#") And (Range("A175:A175") <> "TOUR") Select ...Show All

• Lawrence 007 IsError function from VBA6.3 to v6.0

  Hi All, I'm having problems with a small VBA script that I wrote in v6.3 but need to implement in 6.0. Excel 2002 -> 2000. The following code works fine in the later version: For i = 2 To LastRow ' select the next row based on loop vString = "A" & i If IsError(Application.VLookup(Range(vString).Value, vRange, 31, False)) Then Y = "NEW" Else Y = Application.VLookup(Range(vString).Value, vRange, 31, False) End If ' write the result to the active workbook Cells(i, 31).Value = Y Next i But won't work in the earlier version as there's a type mismatch at the If Iserror ... line. Any ideas why or thoughts on a workaround Thanks, Steve. ...Show All

• Sapna Help with a formula

  I need to write a formula for Excel that calculates the amount of fuel that is in a tank. Here is what it is for. We have cylinder tanks that lay horizontaly. Each month we put a dipstick into the tank to measure how many inches of fuel is left in the tank. I need a formula that will take that and calculate it into gallons. Here is the formula I have to work with. / -1 r - h \ V = L * | [ r^2 * cos (--------)] - [sqrt(2rh - h^2) * (r - h)] | \ r / Any Help is appreciated (BTW I am not the best at math so if possible an explination of this formula would be greatly appreciated) Tiger, forgot ...Show All

• Maclarenf1 Require Basic Excel Sorting Macro To Be Made For Me!!! (Pretty Basic, But I Have NFI!)

  I was wondering if someone could make me some code for Excel 2000. I want it to start at C1, check if C2 is the same as it, and if not insert a row between. Then check if C3 is the same as C4 and if not insert another row. So if my data looked like the left list it would turn out like the right list. Apple                                                 Apple                       &nbs ...Show All

• Mark Shvarts Can any1 arrange the ADO connection logic for this VBA code-block ?

  Hi all VB developers ! Pls. refer to the following code-block. Can any1 arrange the ADO connection logic for this VBA code-block Set mdxRec = New ADODB.Recordset If dbh = "" Then ' MsgBox ("Faild") End If 'dbh.ConnectionTimeout = 360000 Set mdxRec.ActiveConnection = dbh mdxRec.Source = strMDX On Error GoTo errHand mdxRec.Open dbh.ConnectionTimeout = 360000 ' ADDED RECENTLY ''If dbh.Close = False Then If dbh.State = 1 Then dbh.Close End If On Error GoTo errHand Set wbBook = ThisWorkbook Set wkSheet = wbBook.Worksheets(1) 'Read in values from the record set wkSheet.Cells(9, 1).CopyFromRecordset mdxRec SubTotalsIns 9, 2, 3, 4, 8, 9, St ...Show All

• Kent Boogaart Outputting to a Listbox

  Hi; I have a list of data, and need for values outside a lower and upper bound to be flagged up to the user. I was thinking of outputting to a listbox, but cant get the code to work. I was wondering if anyone had any ideas for possible code, or a better way of outputting to the user. Ryan No. I suggest you start a new thread for that question. My list is in H2:H" & FinalRow & " where FinalRow is defined as FinalRow = Range("A65536").End(xlUp).Row The only problem, my list has empty slots. As in i only want cells with values in to be outputted to the listbox. Hi, You real need to provide more detail. Where is the list of data in cells maybe or a control on a userform. ...Show All

• Mansoor Ali Countdown / Automatic Recalc ?

  so...i'm writing a program in Excel for my friend who works at a small pool hall. I've created a countdown (initiated when the user clicks a table) and some other cool stuff. I posted a pic of the excel file here: http://jeremy.haynie.googlepages.com/rackems.jpg so right now I've got the countdown cell (17 min. 15 secs in the pic) as a normal worksheet cell...now - start time. and i've got a macro running every x seconds which makes the cell look like a countdown. I've got vlookup functions bringing back the dollar amount. here is the code i'm using to recalculate and the one to close the workbook. Basically, I've got the recalculate stuck in a loop until the user closes the workbook. Is there a better way to do this Sometimes the workboo ...Show All

• lawlessSaturn Some EXCEL VBA tips

  Does anyone know where I can find a guide to retrieving and updating data from a database into my Excel workbook. I would like the user to enter a date, and excel then connects to the database to get the information and populate a template I've created. Any pointers Ta Cheers. I shall give that a go. I haven't a guide but there is a wizard built into Excel to do what your looking for. If you go Data->Import External Data->New Database Query there is a way there. If you looking for information on how to use this try the help, for example the help points you to two online training pages on how use the wizards involved. http://office.microsoft.com/training/traini ...Show All

• Predator14567 Select query returns non-existing data

  Hello everybody, I'm trying to match entries in a table up against each other based on a number of criteria (Quantity, Date). Three types of entries exist: Those from source 1, 2, and 3. I'm trying to match data from source 1 with data from source 2 and 3. Some is identical, some is not. In a VBA Script I first select every entry from source 1 (indicated by the field "Datasource"). I then run a While loop. For each entry I run a second select query on the fields I want to match (Quantity, Date). It works OK, ie. as expected, in some cases. In other cases, the quantity is completely off - it's as if Access just invents some random quantity instead of using the one from select query one (the one with source 1 data). What on Earth is w ...Show All

• Opfer A simple list of words from a word document

  I want to create a list of words from a word document. For example a document containing a sentence " The quick brown fox " the list of words should be - The quick Brown fox I initially thought I would use the Activedocument.words.count to find the total number of words in the document. Then use a do while loop to iterate through each word and generate a list. The solution was easy but since I am a newbie to VBA I cannot figure out what method to use to generate the list so that every time the loop hits a space after the word, it replaces the space with a newline character till the end of the loop. Could someone give me some ideas / suggestions. Thanks Thanks Andy for your suggestion. ...Show All

• Michael Pritchard 1004 Run-time error

  I tried doing a search for this error message, but couldn't find one related to my problem, so here goes. Running Excel 2000 here and this macro (snippet below) has been running fine on several machines. Suddenly, this one machine in particular has been getting a 1004 Run-time error (With statement). I have an idea what the problem is, but the thing that gets me is this code still successfully works on several other machines at my work place. I know the code is sloppy, as you'll see below. I'm simply trying to fix something that was already in place before I got here. I'll definitely clean it up once I get it working. Any ideas Thanks in advance for the help. -------------------------------------------- Sub maketable() ...Show All