Search results

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

Hello, I don't have open office, so I can not test. 1) Does the macro creates a .bat file in c:\temp? If yes, can you open it and see if the command line is correct? Can you try run it from there? 2) to debug, you can also open the macro, put the cursor on the first line of code starting...

Oups... my mistake...:oops: Please replace this line : If Not Intersect(Target, Columns("E:E")) Is Nothing Then by this line : If Not Intersect(Target, Columns("D:D")) Is Nothing Then So the code will execute only if you double click in column D (where the song name is)

Hello, There's no basic questions... I'm so used to do this that I sometime take for granted that everybody knows how to do it... Here's how to insert your code in your sheet: - From Excel, press ALT+F11 to open the VBA editor - In the left pane, you will see "Project - VBA Project" - Navigate...

Hi pemartins, I didn't download your files but this piece of code should work. You have to copy this code in the sheet module containing your songs. When you DOUBLE-CLICK on a cell in column E (containing the songs names), the macro will create a bat file called "Karaoke.bat" and execute the...

Hi Rob, Here's a proposal using a custom function. See attached file for an example on how to use it. Public Function FindElement(rgList As Range, sTasks As String, bFirst As Boolean) As String ' Input parameters : ' rgList : range of activities, 4 columns in this order : ID, Task...

Hello, Something like this? Sub Test() Dim filename As String Dim currentDateTime As String Dim currentfolder As String currentfolder = Dir(ThisWorkbook.Path, vbDirectory) currentDateTime = Format(Date, "dd-mm-yyyy hh.mm.ss") filename = currentfolder & " " & currentDateTime & "...

Hi Maria, Same concept as proposed by dan_l but with the specifics of your initial code : Sub HistoricalYES() Dim Plant As String Dim rg As Range Dim ar, i As Long Dim bDifferent As Boolean With Sheets("All POs Report") ar = .Range("C1:C" & .Range("C" &...

Hi, Assuming your date is in cell A1, use these 2 formulas in conditional formatting: =TODAY()&#62;A1+365 (for Red) =TODAY()&#62;A1+365-30 (for Yellow)

Hi, Not sure if this is what you are looking for but one method that I use to show that a macro is running, is by updating the status bar shown in the lower left corner in Excel. You need to create a variable used as a counter. For example, if your macro needs to perform calculation on rows 1...

Hi KPS and welcome to the forum, You might want to look at this link from Excel-chart guru Jon Peltier : http://peltiertech.com/WordPress/broken-y-axis-in-excel-chart/ Regards