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?

Caleeco

Administrators
  • Content count

    183
  • Joined

  • Last visited

  • Days Won

    20

Caleeco last won the day on July 26

Caleeco had the most liked content!

Community Reputation

8 Neutral

About Caleeco

  • Rank
    Junior Member 1
  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. Thanks for the feedback Still some great updates to come.. sit tight! Nearly done Caleeco
  10. 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
  11. 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
  12. Search Sheets and Create Summary Table

    Hi Reso, Thanks for the question. There are a number of ways to achieve what you have specified. However, to determine how best to help could you please answer a few follow up questions: Are the sheets names as you have specified, "Dashboard", "SAP Data" and "User Input"? Can the part number appear on both sheets? If so, do you want to list both or display an error? 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? What RANGE on each sheet needs to be searched for the part number? i.e. Column A or Cells A1:A100 etc Let me know Thanks Caleeco
  13. Auto Hide Columns Based on Selection

    Hello Ziya, Glad it's all working now, let me know if you need any mods in the future but feel free to have a go yourself too Yes, you would just alter the ColorIndex property, you can find a list of all the applicable colours (and corresponding numbers) here: http://dmcritchie.mvps.org/excel/colors.htm Also, You may have noticed we have a new Best Answer system, if my last bit of code solved you problem. Have a go at clicking the checkmark next to my post Many Thanks Caleeco
  14. Auto Hide Columns Based on Selection

    hehe yes, thankfully each batch size of data is the same so we can avoid having a SELECT CASE or IF statement Sorry, my mistake! Totally forgot about the coloured cell. I've added a few more lines of code in the main IF statement to cover that. Hopefully light blue fits your colour scheme. Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Source: www.ExcelWTF.com 'Purpose: Auto-hide columns based on cell selection '---- Variables Dim r As Long '---- Code 'Exit Sub if more than one cell is selected or data is deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub 'If selection is in the 'Product Range' If Not Intersect(Target, Range("A2:A11")) Is Nothing Then r = ActiveCell.Row 'Determine the current row number ActiveSheet.Range("A2:A11").Interior.ColorIndex = 0 ' Clear Colours ActiveSheet.Range("A" & r).Interior.ColorIndex = 37 'Colour the correct cell Columns("B:AY").EntireColumn.Hidden = True 'Hide all columns 'The code below works out the Starting column number for each batch of data If r > 2 Then r = 2 + (r - 2) * 5 'Unhide the data in column r and the next 4 columns Range(Columns(r), Columns(r + 4)).EntireColumn.Hidden = False End If End Sub Thanks, Caleeco
  15. Hello, Just a quick post to share with you a great little formula, =FORMULATEXT(CELL) You can use this within a worksheet to show end-users what formulas are being used in the background for your calculations. As you can see, the function extracts the formula as text, and will be treated by Excel as a text entry. Hope that helps Caleeco ExcelWTF Owner
×