A very bad formed question for this site. It’s good for you to spend some time to read the rules. Anyway, this is from me. Const ForReading = 1, ForWriting = 2 Dim FSO, FileIn, FileOut, strTmp Set FSO = CreateObject(“Scripting.FileSystemObject”) Set FileIn = FSO.OpenTextFile(“shar.txt”, ForReading) Set FileOut = FSO.OpenTextFile(“sha.txt”, ForWriting, True) Do Until FileIn.AtEndOfStream … Read more
The following will achieve what you are wanting, it will generate 18 random numbers between 2 and your last row with data, in your case row 180 and then copy that row into the next free row in Sheet2: Sub foo() Dim wsOriginal As Worksheet: Set wsOriginal = ThisWorkbook.Worksheets(“Sheet1”) Dim wsDestination As Worksheet: Set wsDestination … Read more
try the code below : Sub test() Dim ws As Worksheet Dim ws2 As Worksheet Set ws = ThisWorkbook.Sheets(“sheet1”) Set ws2 = ThisWorkbook.Sheets(“sheet2”) lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastRow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row i = 1 Do While ws.Cells(i, 1).Value = ws2.Cells(i, 1).Value i = i + 1 Loop For j = i To lastRow lastRow2 = … Read more
Read up on your Visual Basic for Applications data types here on MSDN Code example for the basic numeric types: Sub Some_sub() Dim a As Double Dim b As Long Dim c As Integer Dim d As Single a = 0.02 b = 0.02 c = 0.02 d = 0.02 Debug.Print a ‘output: 0.02 Debug.Print … Read more
While Den Temple is correct, and you really should Dim variables independently, the real problem here is with the logic of: For clm = 1 To 5 ClmTtl = 0 For copyRow = 1 To percRows ClmTtl = ClmTtl + Sheets(1).Cells(MyRows(copyRow), clm).Value Next Next This clears ClmTtl each time, without doing anything with the total … Read more
SOLVED ! Search in the registry for correct application name. On windows 7 you can find it in “HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\RegisteredApplications”. Then replace the new name in “Set wrd = GetObject(, “Word.Application”) Thanks to @pavanc It was called Word.Application.16 instead of Word.Application solved GetObject(, “Word.Application”) Office 365
Introduction This tutorial will provide a step-by-step guide on how to create a loop in Excel VBA to print the text “x” in cells A5, B4, C3, D2, and E1. This loop will be created using the For Next loop, which is a type of loop that allows you to repeat a set of instructions … Read more
Here’s one way you can do this with a loop: Sub x() Dim i As Long For i = 1 To 5 Cells(6 – i, i).Value = “x” Next i End Sub 0 solved How to create a excel VBA loop to print text(“x”) in cell A5,B4,C3,D2,E1
This should be rather simple. You need to create 1 cell somewhere in your file that you will write the cell count for column Y each week after removing all dupes. For example, say week1 you remove dupes and you are left with a range of Y1:Y100. Your function will put “100” somewhere in your … Read more
Try this example using strings: To get you started, you could use Cells(row#, column#), instead of Range(“A1”) and perform loops over the array and loop through the cells by row, comparing each value. Then adding the values that don’t match to a new array, that you will later pass back into the sheet in another … Read more
In an unused column to the right, put this array formula¹ in the second row. =ISNUMBER(MATCH(LEFT(A2, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A2)-1, 1E+99)))&MID(A2, FIND(“.”, A2), 9),A:A, 0)) Finalize with CSE and fill down as necessary. Use Data ► AutoFilter to filter on that column for TRUE and delete the visible rows. ¹ Array formulas need to be finalized with … Read more
I was able to solve it by using the Workbook.Query object. here is my solution. Public Sub FunctionToTest_ForStackOverflow() ‘ Doug.Long Dim wb As Workbook ‘ create empty workbook Set NewBook = Workbooks.Add Set wb = NewBook ‘ copy queries CopyPowerQueries ThisWorkbook, wb, True End Sub Public Sub CopyPowerQueries(wb1 As Workbook, wb2 As Workbook, Optional ByVal … Read more
I finally found another way to do this. I loop on the paragraphs : For Each objPara In Selection.Paragraphs With objPara.Range sText = .Text sStyle = .ParagraphStyle ‘On détermine le style de l’élément courant s’il en a un If sStyle = “Titre 1;H1” Then If sH1 <> sText Then sH2 = “” End If sH1 … Read more
Do like this. Sub test() Dim vDB, vR() Dim i As Long, n As Long Dim s As String vDB = Range(“g1”, Range(“g” & Rows.Count).End(xlUp)) n = UBound(vDB, 1) ReDim vR(1 To n, 1 To 2) For i = 1 To n s = vDB(i, 1) vR(i, 1) = Split(s, “https://stackoverflow.com/”)(1) vR(i, 2) = Split(s, … Read more
Or this way: Sub AssignWSobjectsToWorksheets() Dim i As Integer, n As Integer Dim ws() As Worksheet n = ThisWorkbook.Worksheets.Count ReDim ws(1 To n) For i = 1 To n Set ws(i) = ThisWorkbook.Worksheets(i) Next End Sub Note that Sheets is the collection of all tabs(e.g. including charts), Worksheets just of spreadsheets solved loop to set … Read more