Posts by vwankerl

    Re: Excel to VBA if statement conversion


    I assume you wanted to use VBA to extract the time. Here is a short procedure that demonstrates one way to do it:


    Code
    Sub Test()
       Dim Test1 As Date, Test2 As Double, Test3 As Date
       Test1 = Range("B4").Value
       Test2 = Test1 - Int(Test1)
       Test3 = CDate(Test2)
       Debug.Print Test1, Test2, Test3
    End Sub


    The first statement gets the value from the cell. It just needs to a valid date/time value in the cell and does not require that the cell be formatted as Date/Time. The second statement removes the Date portion of the value. In case you didn't know, in Excel a date/time is just a Double value in which the value to the left of the decimal point is the Date and the value to the right of the decimal point is the Time. The third statement just converts the Double value in Test2 to a Date variable. Hope this helps.

    Re: Copying cells based on colour


    Attached is your workbook with VBA code added to do what you wish to do. Note that there is the Destination worksheet to copy to. Also note, if there is a cell in E and F that are Red then you will have two copies of the row in the Destination worksheet. What I did in the VBA code is to use the code from C Pearson website to determine if the Conditional Formatting is active. Then will copy the proper cells to the Destination sheet.


    In order to run the code, open the workbook, press Alt-F8 and select the copy_red macro and click Run.


    If desired, I will try to explain the code (mine, not Pearson's).

    Re: Copying cells based on colour


    Jeswin,


    I must point out to you that the code that Fackouch posted will not do the job for you. Since you use Conditional Formatting to set a color, you will not be able to use the cell.Interior.Color (or ColorIndex) to test the value. It will only report the Interior color of the cell if it is not red. Did you visit the site I referenced about this? I will try to put together some VBA to help you. Just know that Conditional Formatting is not easy to work with in VBA.

    Re: Locate 3 columns in an excel sheet,do calculations among those columns,paste resu


    If the column headers and their location(row) are known, use the Range.Find to determine the proper columns. Then in each of the two result columns, set the proper formula into the cells as necessary. You should then be able to copy the total of the two result columns to the proper cells in the other worksheet. All this is fairly simple, just use the Range.Find method to determine the columns to work on.

    Re: Copying cells based on colour


    Unfortunately, conditional formatting does not produce any setting that is testable in VBA. Conditional Formatting is strictly to highlight information for the user to see. As you have no doubt found out, testing the Interior color of a cell that looks Red because of Conditional Formatting shows that the Interior color is not Red but whatever color the cell has as a default.


    here is a site that talks about this and gives some help:


    http://www.cpearson.com/Excel/CFColors.htm

    Re: Run-time error '1004' Application-defined or object-defined error


    First off, I suggest you remove the With Range() statement since it only applies to the line that follows it. Combine the two into one statement.


    Second, you must understand that all the Range and Rows references are for the ActiveSheet in ThisWorkbook. Usually when you have the 1004 error, you are trying to reference a cell that is not in scope at that point. If you remove the With statement as in First step, you might solve the problem.

    Re: Conditional Formatting Issues


    I checked out your workbook and have some suggestions:


    make the first Rule for all the Conditional Formatting to be =AND(NOT(IsBlank($B4)),IsBlank($C4)) or $D4 or $E4 format to be Pink


    Make the second Rule for column C to be =$C4<>"NKA" format to Red


    Make the second Rule for column D to be =($D4-$A$1)<180 format to Red check to Stop if True


    Make the third Rule for column D to be =($D4-$A$1)<356 Format to Yellow (I just realized should that be 365 not 356? Same formula just fix number of days)


    Make the second rule for column E to be =($E4-$A$1)>365 format to Red


    Only second rule on column D should be marked to Stop if True.


    See if these will work for you.

    Re: Capturing data with value and color fills to another sheet


    I am not sure exactly what you are trying to do, but if it involves testing the Cell fill color that is set by a Conditional Format then know that such a need has a non-trivial solution. You will need to do the same tests in VBA that are programmed into the Conditional Format condition to determine if the Conditional Format has been activated. There is no simple test of the Cell interior color available! Sometimes it is just better to use normal formulas in another column to produce a value result that is equivalent to the Conditional Format if you need to test in VBA (or even in other formulas). For the most part, Conditional Formatting is strictly a User Interface (highlight a cell so the user can see it) feature.

    Re: Macro to search for a paritcular word in cell and copy columns to a new workbook


    In order put your code in Tags, use the "#" tool on the toobar above the Reply edit box. By pressing that tool you insert into your text "". Then you merely insert your code between the center two "][". You can also simply type the two tags manually, one at the top of code and one after the end of code. It is recommended that you edit your original post to add these tags.

    Re: Vlookup - Code running but not populating values


    I am not sure about the code you have here. In the first section you select a column and put a title on it. The next section appears to search through another column in a second worksheet for a value that was in cell B1 of the first sheet. If you find that value on the second sheet, you take the value of the cell to the right of it and put it into a variable called "VLookup".


    Please note that VLookup is a VBA variable, not some value that you might expect if you were using the VLOOKUP function in a formula!!


    Also, there does not appear to be any connection between the two sections of code.


    Since you do not give any details about the structure of the "combined" worksheet, we can't really assist you with the code.


    For all intents and purposes, you have a do nothing macro that runs with out error.

    Re: Macro Help If/Then Statements


    If you wish to test the current value of C1 after you put the formula into the cell, you will need to do something like this:



    As it is now your code merely tests the value of cell C1 that was there at the beginning of the procedure.

    Re: Run procedure on Sheet1, then check for colored cells in Sheet2


    If you are using Conditional Formatting to set the color of the interior, you can not use the Cells().Interior.ColorIndex to test what you see. You will always get the base color of the cell. In order to determine the Conditional Formatted color you must do the same tests that are configured in Conditional Formatting in the VBA code. I have done some of this for another forum member. If you could send me a sanitized version of you workbook via private email (I will private message you with the address), I will look at what can be done.

    Re: Problem with code


    You don't tell us the error or version of Excel. However, looking in Excel 2003 and 2007 there is no property "ActiveWorkbook.SavedName" which is what you are using in the ChangeLink line.


    Also, you could get a runtime error on the last line of the sub if the changelink line actually executes since it will try to close a workbook that is already closed.

    Re: Moving Sheets Causes Data Not to Transfer


    AmyKate,
    Below the Quick Reply edit box is a button labeled "Go Advanced". Click that to get to the Advanced Reply edit box. Below that is "Additional Options" box. There you will find "Manage Attachments". Click there to get to the file upload process. Please note the size limits.

    Re: Scrolling Text in a cell


    For those of us who do development/programming for a living, we need to understand the problem and the reasoning behind the desired solution. Most times, the problem can be solved in multiple ways with various trade-offs considered. For example, you can use a VLOOKUP formula in a cell to find some item in a list of cells or you can code a UDF in VBA to do the same thing, but each has different requirements and trade-offs to consider. As cytop suggests, there is probably other ways that may be more efficient or work better for you.

    Re: Parse imported CSV file into columns/rows


    This is a much different problem than what you originally posted. I am afraid I will have to defer to others who have experience with Web access and database processing. You might want to edit your subject line or start an new thread with a more descriptive subject line to grab the eye of others who could help you.

    Re: Moving Sheets Causes Data Not to Transfer


    It would be helpful to see at least a demo of your workbook. Be sure to test the demo to be sure it behaves just like the one with the problem. You can post a small file here. If you can not make the demo small enough to post and still show the problem, I would entertain the possibility that you upload to me privately a bigger demo or sanitized original file.