• WSCardfaninKC

    WSCardfaninKC

    @wscardfaninkc

    Viewing 15 replies - 1 through 15 (of 128 total)
    Author
    Replies
    • in reply to: If/Then based on day of week #1184835

      You can test Weekday(Date):

      Code:
      Sub Test()
        Select Case Weekday(Date)
      	Case vbSunday
        	' Code for Sunday
      	Case vbMonday
        	' Code for Monday
      	Case vbTuesday
        	' Code for Tuesday
      	Case vbWednesday
        	' Code for Wednesday
      	Case vbThursday
        	' Code for Thursday
      	Case vbFriday
        	' Code for Friday
      	Case vbSaturday
        	' Code for Saturday
        End Select
      End Sub

      That worked great. Thanks!

    • in reply to: Copy/Paste error #1164165

      I just noticed the sentence “The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.”. This means that the source and destination sheet don’t have the same number of rows. What happens if you copy a specific range, e.g.

      Range(“A1:D1000”).Copy Destination:=ThisWorkbook.ActiveSheet.Range(“A1”)

      (This instruction combines copy and paste)

      I messed around with that a bit and I couldn’t get that to work. However, I may not have been doing it right. I’ll give it a shot and see what happens.

      One question (and more info about what the macro is doing)…the spreadsheet that contains the data is not always named the same…it’s based on a selection made. The same selection also provide the information for the new spreadsheet, which is where the data will be pasted. Will that make any difference?

      And also, how does the code know which “ThisWorkbook” to paste to? At this point in the code, the spreadsheet that has the data I want to copy is open and active.

      Edited to add…Nevermind the above questions. It worked and I realize how it knew which workbook. It’s the workbook that contains the code. Thanks!

      Thanks for being patient with me. I really do appreciate the assistance.

    • in reply to: Copy/Paste error #1164150

      Do you have merged cells in the source sheet or destination sheet? That is a known cause of problems when copying and pasting.

      No, nothing is merged.

      There is more code than what I’ve pasted, so I guess it’s possible that something elsewhere is causing the error. But this portion of the code is up near the top and the only thing before it is the opening of the other spreadsheet and the creation of a new worksheet.

      I have also inserted at the beginning of the macro “Application.CutCopyMode = False” to clear out the clipboard.

    • in reply to: Assign Different Colors #1163565

      Here’s a macro using 10 colors. You can easily expand the number of colors used.

      Very nice!

      It does work in column A, which is fine. What part of the code “tells” it to work in that column? Is it this part: strCity = Cells(r, 1)

      If so, then I could change the 1 to whatever column reference?

      Thanks again for your help.

      Edited to add:

      Looks like the 1 referenced in my question above refers to a cell offset. I moved my list to Column F and changed it to a 6. It ended up still coloring Column A, but based on the data in Column F.

    • in reply to: Assign Different Colors #1163520

      I’m using 2007. I didn’t consider the human limitations. Thanks for pointing that out.

      So no that I know they need to be grouped, is there a way to assign various colors? And if there are more in my list, I can just start over with the color assignments.

    • in reply to: Page Break on every Cell (Excel 2003 SP3) #1163514

      I used to encounter this problem quite a bit with older versions of Excel. I never found out what caused it, but the remedy for me that worked every time was to uninstall (or delete) the printer and then add it back. That solved it for a few weeks and then when it cropped up again, I’d just repeat.

      And I didn’t have to uninstall the drivers or anything…just delete the printer from the printer control panel.

    • in reply to: ComboBox in a Userform #1162584

      If selecting an item in the combo box made the userform go away, the user wouldn’t be able to correct mistakes. So I’d place a command button cmdClose next to the combo box with the following code:

      Code:
      Private Sub cmdClose_Click()
        Unload Me
      End Sub

      Very nice.

      That did it. Thanks for the tips and the help.

    • in reply to: ComboBox in a Userform #1162575

      You can set the RowSource property of the combo box to R1:R49 in the Visual Basic Editor instead of in code, and you can set the ControlSource property to the address of the cell to which you want to link the value of the combo box, e.g. A1 – again, no code necessary.

      Hey, thanks for the pointer on that. I got it to work, but it only populates the cell when I click the X to close it.

      Now that I’m not using code, I’m not sure how to make the userform go away.

    • in reply to: Highlight cells based on input #1154654

      Hello again.

      Thank you for trying to help me. It is much appreciated. I do not think conditional formatting will work. There will be some instances where I do not need a row colored even though it meets the conditions. The conditional formatting overwrites all formatting. I will keep digging around to see what I can come up with.

    • in reply to: Highlight cells based on input #1154631

      Thanks for the responses. I’ve tried conditional formatting and it won’t do what I’m trying to do. The cell contains both the date and time and I’m looking have it highlight based on the time only. In my format example above, let’s say I want it to highlight anything after 6:00PM. The example above would need to be highlighted regardless of the date. The spreadsheet is updated with new data daily and the time that needs highlighted will not always be the same.

      I’ve tried entering the time into an inputbox and then concatenating it with a Today() formula and I can’t get that to work either. Copying and pasting the result as values gives me a number value that does not correspond with the correct date.

    • in reply to: Consolidate data #1151870

      Can you post a sample worksheet so we can see your current set up? Include a the before data and what you would like the results to look like when things are finished.

      If you have 2007, please save the book to a previous version.

      I have attached a sample spreadsheet. There are some blank cells in column C on purpose. Some come through that way.

      Also, the physical address in column B may sometimes be different, and I need those to remain a separate line, but to also consolidate any email addresses associated with the customer name.

      And sometimes the record is exactly the same as another one that appears.

      Sure do appreciate it.

    • in reply to: Web Query #1151860

      Unfortunately, that article does not clearly explain how to enable parameters for web queries. I do that here:
      http://www.jkp-ads.com/articles/webquery.asp

      Hey thanks!

      That looks to be a very clear explanation. I’ll mess around and if I have any questions I will let you know.

      Thanks again!

    • in reply to: Conditional Formatting using multiple cell references #1148692

      Sorry to continue asking questions. I’m either doing this wrong or I’ve not explained what I’m looking for.

      I am using Excel 2007 if that makes any difference.

      I’ve pasted the formula you provided in the Rule Description part of the dialog. I do not receive an error, but no cells are formatting.

      In 2007, it allows you to choose the area where you want the formatting to take place in a different step.

      I have over 2300 rows and this will continue to increase each day as I add more data.

    • in reply to: Conditional Formatting using multiple cell references #1148671

      I apologize for not being more clear.

      The cell that contains the date is in column A, and if the date matches any of the dates in AL4:AL30, I want the row colored a specific color.

    • in reply to: Save copy of workbook (2007) #1147697

      That did it.

      Thanks!

    Viewing 15 replies - 1 through 15 (of 128 total)