Recent content by GCExcel

Hi, I think this should work: .Value = .Parent.Evaluate(Replace("IF(#=""NA"",""N/A"", UPPER(#))", "#", .Address))

Hi nagovind, Why do you need to reorder the sheets? If it's because you don't want to "see" the "APsheets", you could hide them instead? (just trying to understand your use case as their might be an alternative solution). Otherwise, you could have a hidden sheet where you would store the...

My bad, I just saw the error, add a "." in front of Cells .Range(.Cells(x + 3, c), .Cells(256, c)).Copy

Can you share the rest of the code or even better your workbook?

Hi, Here's a suggestion for you : Sub Separar2() Dim g As Long Dim y As Integer, k As Integer Dim código As String Dim respuesta(1 To 100) g = Hoja1.Range("B" & Rows.Count).End(xlUp).Row Range("D" & g & ":" & "AZ" & g).Interior.Color = xlNone 'Separar los numeros en cada celda...

Hi, It's because you use Activesheet to refer to the sheet you want to apply the For/Next. Since the code is inside a sheetcode, the activesheet is always the same (ie that sheet code). When referring to a range or a cell, if is good practice to refer to the sheet name to prevent such problem...

Hello, Here's a suggestion for you. Sub TransposeData() Dim ar Dim Dic, a, b, e Dim i As Long, str As String Set Dic = CreateObject("Scripting.dictionary") ar = Range("A4").CurrentRegion.Value 'Store values in array '/ Optional - to sort array on Status_Age ReDim...

Hello, Add your sheet called "Output" in your Provider List file, then run this code : Sub CompileData() Dim Dict Dim ar, a, b, c Dim i As Long, j As Long Dim sFullName As String ar = Sheets("Data").Cells(1).CurrentRegion.Value Set Dict =...

Hi webmax Maybe like this? Sub autosum() Dim R As Long lrow = Range("B1").End(xlDown).Row Range("F1").End(xlDown).Offset(1).Formula = "=SUM(R[-" & (lrow - 1) & "]C:R[-1]C)" Range("B1").End(xlDown).Offset(1).Formula = "=SUM(R[-" & (lrow - 1) & "]C:R[-1]C)" End Sub

Hello, Have a look at the attached file. Macro is called "Test" in the Module "M_Orders". I used a custom class to get the results...

@Oxidised You can go in the properties (shortcut = F4) and change the sheet codename to whatever you want. Example: Sheet1 >> shData and then in your code : Sub Sub CopyIt() shData.Cells(RowCustomer, CurrentCol).Copy shMailMerge.Cells(CurrentRow, ColCustomer).PasteSpecial xlPasteValues...

@SirJB7 a) If you want to skip rows 15 and 19, I believe you need to use AND not OR. Try this simple code, and see by yourself : Sub test() Dim i As Integer 'Want to skip 5 and 9 'This works : For i = 1 To 10 If i <> 5 And i <> 9 Then Debug.Print i Next i...

Hi, See suggestion in the attached file. I've used a custom class to handle all those data. Macro is in the module call "M_MailMerge". You will also need the 2 classes "cCustomer" and "cCustomers".

Hi, Avoid using .Select to make your code run faster. And this should probably be an AND instead of an OR to skip rows 15 and 19 : Sub Goal_Seek() Dim i As Integer Dim x As Double x = 0 With Sheets("Prime Total Returns") For i = 10 To 21 If i <> 15 And i <>...

Hi, to my knowledge, you can switch between absolute/relative reference only for the "selection" of cells. All formulas are recorded using the R1C1 style in VBA. If you want to see the formula in A1 style, type this command in the execution window : ? activecell.formula