Jump to content
Welcome Guest!

Join us now to get access to all our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, and so, so much more. It's also quick and totally free, so what are you waiting for?

All Activity

This stream auto-updates     

  1. Earlier
  2. Welcome to Pages

    Welcome to Pages! Pages extends your site with custom content management designed especially for communities. Create brand new sections of your community using features like blocks, databases and articles, pulling in data from other areas of your community. Create custom pages in your community using our drag'n'drop, WYSIWYG editor. Build blocks that pull in all kinds of data from throughout your community to create dynamic pages, or use one of the ready-made widgets we include with the IPS Community Suite. View our Pages documentation
  3. Excel adding 2 decimal places after editing a formula

    Hello Spacecityguy, Thanks for joining the forum! Could you please tell me which version of Excel you are using, as I don't seem to be able to recreate this problem. Or better yet, upload a sample file where this problem exists. I tired the following: Populated A1:A5 with decimal values Populated B1:B5 with decimal values set up a formula below in cell C1 and change the format to Number > 0 decimal place Code: =SUMPRODUCT(A1:A5,B1:B5) If I change the values in columns A or B, or if I change the Range of the arrays (in the sumproduct) the number formatting does not change. I am running Excel 2016. I look forward to hearing from you Caleeco
  4. Hi all, I thought I was a proficient Excel user, and I'm ashamed to say that I can't figure out how to solve this through this years (have done web searches every now and then). I just put up with it, but when I think how much time I have spent simply correcting Excel's automatic formatting, it hurts. I build a lot of Excel templates/models and often use complex formulas. I noticed ever since Excel 2007, when I edit a formula in a cell with number formatting applied to show whole numbers (no decimals) beforehand, Excel will ignore my number formatting and insert 2 decimal places to the result. I have tried the advanced option on automatic inserting decimal places and also pre-format all cells used in the formula have the same format but to no avail. Anyone know what causes this behavior and how to fix it? I initially thought it was a bug in Excel 2007 and MSFT would fix it in the next version, but it doesn't seem to be the case. So I think I'm missing something. Thanks.
  5. Create Address Label using User Data

    Hi Caleeco, So sorry for not getting back to you sooner, work-life has been busy! Ahhh ok, I'm clueless when it comes to arrays... I'm still learning the basic macro stuff YES!!! Some tutorials would be great! Thanks for breaking down the problem, at least I now know how to spot arrays Z
  6. Create Address Label using User Data

    Hi Ziya, No problem, I'm happy to help! With regard to the alignment , each cell has an INDENT property we can make use of. So towards the bottom of the code please edit this WITH statement: Code: With shLabel.Columns("A:C") .ClearContents .VerticalAlignment = xlVAlignCenter .IndentLevel = 5 End With Change the INDENT LEVEL to suit your requirement. Aha, you're half right there. The variable Label is a string, but it is an ARRAY of strings. As declared at the start, the parenthesis are the clue to knowing its an ARRAY rather than a variable. Code: Dim Label() As String You can think of an array like a table of data, the size of the table can be whatever you like. In this instance, i changed the size of the array to match how many rows of data we had (adjusting -1 to account for the fact the data starts in row 2). Code: lr = shYear.Range("I" & Rows.Count).End(xlUp).Row 'Find the last used rowReDim Label(lr - 1) 'Set the size of the array Each row of an ARRAY is called an ELEMENT. So i created a loop, to fill each ELEMENT with 1 address from your 2017 sheet If you would like a more in depth explanation I would be happy to talk you through it. However, I will be starting a blog on the website very soon to teach people this sort of thing Caleeco
  7. Create Address Label using User Data

    Thanks Caleeco! I sort of followed what you did, but I'm not good enough at VBA to understand all of it! Not sure how 1 string variable Label gets used to make separate address entries I like how it fills up the page left to right, and then top to bottom However, the cells are left aligned.. is there a way to have them more centralised? As I will be printing these on sticky paper, dont want the addresses right on the edge. Z
  8. Create Address Label using User Data

    Thanks for the additional information. I have put some code together for you to test! Note: The Option Base 1 statement must appear at the top of your code Code: Option Explicit Option Base 1 Sub CreateLabels() 'Source: www.ExcelWTF.com 'Purpose: To create address labels using data from excel sheet '----- Variables Dim shYear As Worksheet, shLabel As Worksheet Dim lr As Long, i As Long, j As Long, k As Long Dim Label() As String Dim r As Range, RNG As Range '----- Code i = 1 Set shYear = Sheets("2017") 'Change the line below next year if you create a new sheet lr = shYear.Range("I" & Rows.Count).End(xlUp).Row 'Find the last used row ReDim Label(lr - 1) 'Set the size of the array Set RNG = shYear.Range("F2:F" & lr) 'Set the data range to loop through Application.ScreenUpdating = False On Error Resume Next For Each r In RNG 'Collate information in the array If Not IsEmpty(r.Value) Then Label(i) = Label(i) & r.Value & " " 'Title If Not IsEmpty(r.Offset(0, 1).Value) Then Label(i) = Label(i) & r.Offset(0, 1).Value & " " 'Initial If Not IsEmpty(r.Offset(0, 3).Value) Then Label(i) = Label(i) & r.Offset(0, 3).Value & vbCrLf 'Surname If Not IsEmpty(r.Offset(0, 4).Value) Then Label(i) = Label(i) & r.Offset(0, 4).Value & vbCrLf 'Add 1 If Not IsEmpty(r.Offset(0, 5).Value) Then Label(i) = Label(i) & r.Offset(0, 5).Value & vbCrLf 'Add 2 If Not IsEmpty(r.Offset(0, 6).Value) Then Label(i) = Label(i) & r.Offset(0, 6).Value & vbCrLf 'Add 3 If Not IsEmpty(r.Offset(0, 7).Value) Then Label(i) = Label(i) & r.Offset(0, 7).Value & vbCrLf 'Add 4 If Not IsEmpty(r.Offset(0, 8).Value) Then Label(i) = Label(i) & r.Offset(0, 8).Value & vbCrLf 'Add 5 If Not IsEmpty(r.Offset(0, 9).Value) Then Label(i) = Label(i) & r.Offset(0, 9).Value & vbCrLf 'Add 6 If Not IsEmpty(r.Offset(0, 10).Value) Then Label(i) = Label(i) & r.Offset(0, 10).Value 'PostCode 'Debug.Print Label(i) i = i + 1 Next r 'Print the data to the labels sheet Set shLabel = Sheets("Labels") With shLabel.Columns("A:C") .ClearContents .VerticalAlignment = xlVAlignCenter End With j = 1 k = 1 For i = LBound(Label) To UBound(Label) shLabel.Cells(j, k).Value = Label(i) If k = 1 Then k = 3 ElseIf k = 3 Then k = 1 j = j + 1 End If Next Application.ScreenUpdating = True End Sub Let me know how you get on Thanks Caleeco
  9. Create Address Label using User Data

    Hey Caleeco, Yes if you could please omit the first name that would be good. Surname should always be filled out so that can be used to find the end of the data. I will probably add a Unique ID for each entry at a later date, but we use last name for now. Kind Regards Z
  10. Create Address Label using User Data

    Hi Ziya, I should be able to help out with that. With the data and example you have provided. I assume you do not want the first name (Column H) to be included? Also, in order to determine the last row used, I will need a column that will always have data. Could you let me know which column will always be filled in? eg Surname? Thanks Caleeco
  11. I have a spreadsheet that contain some names and addresses for clients. The Data is on a sheet called "2017" and is layed out like this: The first entry is on Row 2 and ends Row 50 (but that can change). Can someone help me write a macro so that it can Create and address for each line like this (skipping blank cells): Miss Z Sepp 256 Hollywood Ave Fairfied New Jersey NJ 00074 And have each pasted on a sheet called "Labels". This is to enable printed labels to be created with addresses already on them. I have uploaded the Lables sheets but not the DATA as it has sensitve information on them. If you have any follow up questions please let me know Thanks Z Labels.xlsx
  12. Auto Hide Columns Based on Selection

    Hey Caleeco, I dont actually see that tick box, I can only vote answers up or down. Is there something I have to enable? Z
  13. Search Sheets and Create Summary Table

    Hey Caleeco! Thanks for the added error message, everything is up and running now. This will be a huge timesaver! Doing this manually everytime was getting tedious
  14. Search Sheets and Create Summary Table

    Hi Reso, Sure thing, that's just a small edit. Add the following snippet before the last line (where the location is printed to the dashboard) Code: If Found = False Then MsgBox "Part Number " & PartNo & " not found in either dataset", vbCritical, "Error" Exit Sub End If Hope that helps Caleeco
  15. Search Sheets and Create Summary Table

    Hey Caleeco, Just got back home and spun the code through. Everything seems to work fine. One thing I noticed, when the part number is not found on either sheet... I don't get any error message? Is it possble to get a pop-up thats says "Part Number not found on either dataset"? Thanks for your help!
  16. Code Tidy Up

    Hello Kirabo, Thanks for the Query. I have consensed the code best I can, hopefully its clear to see where the inefficiences were eliminated: Code: Sub CodeTidy() 'Source: www.ExcelWTF.com Rows("2:3").Delete Shift:=xlUp With Range("A1:M2") .Interior.ThemeColor = xlThemeColorAccent1 .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With Range("A3:M4").Copy Range("A17") Range("A22").FormulaR1C1 = "=SUM(R[-19]C:R[-1]C)" Range("A22").AutoFill Destination:=Range("A22:M22"), Type:=xlFillDefault End Sub As you can see, removing all the SELECT statements saves a lot of line. You usually never would need to select a cell in Excel. Also when recording macros, Excel tends to verify related properties, and set thems to their default value. This is unnecessary as they are not changing and already at their default value (this can be see where you change the cell colour and alignment). Hope that helps Caleeco
  17. Search Sheets and Create Summary Table

    Hello Caleeco, That's ok, I've been a bit with some other projects too. Thanks for the VBA code, I will test it a bit later today and let you know if I have any problems!
  18. Code Tidy Up

    I have recorded a macro to do some operations I need to complete on imported data. It saves me some time and lets the VBA do the tedious bit of the task for me. However, I know that recording macros isnt the most efficient task. Has anyone got any suggestions of how I can tidy this code up? Code: Sub Macro1() ' ' Macro1 Macro ' ' Rows("2:3").Select Selection.Delete Shift:=xlUp Range("A1:M2").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A3:M4").Select Selection.Copy Range("A17").Select ActiveSheet.Paste Range("A22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-19]C:R[-1]C)" Range("A22").Select Selection.AutoFill Destination:=Range("A22:M22"), Type:=xlFillDefault Range("A22:M22").Select End Sub Any help would be great Kind Regards Kirabo
  19. SUMMER VACATION

    I like the Donator and Staff label things you have on the top right! Pretty cool I have a trip planned in June with some friends to go to Miami, should be pretty lit! Looking forward to it Z
  20. Hi

    Welcome to the forum!
  21. SUMMER VACATION

    How quickly a year passes! How are you doing Nast, what you got lined up this summer? Glad the weather is finally turning up! Winter will not be missed
  22. Thanks for the feedback Still some great updates to come.. sit tight! Nearly done Caleeco
  23. Search Sheets and Create Summary Table

    Hi Reso, Thanks for the additional information. Apologies for the delay in getting back to you, upgrading websites can be very time consuming Give this code a spin and let me know if it works on your end: Code: Sub FindPart() 'Source: www.ExcelWTF.com 'Purpose: To locate part number and list found location '----Variables Dim PartNo As String, Location As String Dim ws As Worksheet, wsDash As Worksheet, wsSAP As Worksheet, wsUser As Worksheet Dim c As Range Dim lr As Long Dim Found As Boolean '----Code Set wsDash = Sheets("Dashboard") Set wsSAP = Sheets("SAP Data") Set wsUser = Sheets("User Input") 'Set the PartNo to search for PartNo = wsDash.Range("A4") 'Search for the Part Number, setting a dynamic range in column C of SAP DATA lr = wsSAP.Range("C" & Rows.Count).End(xlUp).Row Set c = wsSAP.Range("C2:C" & lr).Find(PartNo, , xlValues, xlWhole, xlByRows, , True) If Not c Is Nothing Then Location = wsSAP.Name & " - " & c.Address(0, 0) Found = True End If 'Search for the Part Number, setting a dynamic range in column C of User Input lr = wsUser.Range("C" & Rows.Count).End(xlUp).Row Set c = wsUser.Range("C2:C" & lr).Find(PartNo, , xlValues, xlWhole, xlByRows, , True) If Not c Is Nothing Then If Found = True Then MsgBox "Part Number " & PartNo & " was found on both sheets. Exiting macro...", vbCritical, "Error" Else Location = wsUser.Name & " - " & c.Address(0, 0) End If End If 'Print location to Dashboard sheet wsDash.Range("D4").Value = Location End Sub Thanks Caleeco
  24. The new website looks great, certainly an upgrade! More emoticons would be nice I could do with some more confused ones... as that's my expression most of the time when using VBA Z
  25. Hi

    Hi Reso, Thanks for stopping by and for the kind words I'm just working on your VBA request now, should post back soon Thanks Caleeco
  26. Search Sheets and Create Summary Table

    Hello Caleeco, Thank you for the reply! Ok I have listed my answers to your queries: Are the sheets names as you have specified, "Dashboard", "SAP Data" and "User Input"? Yes Can the part number appear on both sheets? If so, do you want to list both or display an error? It shouldn't, but an error would be good. Can there be multiple entries of the part number in each sheet? If so, do you want to find the first one only? or all? Yes, but I'm only interested in the first one found (top to bottom, as they are sorted by deliverable date) What RANGE on each sheet needs to be searched for the part number? i.e. Column A or Cells A1:A100 etc Cells C2:C? on both sheets (last row can change) Message me if you need any more info, thanks again
  1. Load more activity
×