cells1

The code below works if there is data in A1 and a neighbouring cell. Dim MyArray() As Variant MyArray() = Cells(1, 1).CurrentRegion.Value However, if there is only a value in A1, it crashes. How can I make it work when there is only one value? Thanks

The code below works to remove duplicates: Option Explicit Sub test() Dim DIC As Scripting.Dictionary Set DIC = New Scripting.Dictionary Dim MyArray() As Variant Columns(3).ClearContents MyArray = Cells(1, 1).CurrentRegion.Value Dim n As Long For n = 1 To UBound(MyArray, 1)...

Hi! I have characters which are recognized by Excel itself, however, when referring to them from the VBA editor something gets wrong. For example if I write the expression of ? Cells(1,"A").Value into the Intermediate Window, I get "a?" so VBA can't recognize. (Latin characters) Excel...

Hi Guys. I want to copy the last row with data and paste in the last empty row using macro but I am finding it difficult. This is what I have been trying since but it is not working. Sub test() Dim ws As Worksheet Set source = Sheets("Total") Dim LC As Long With ThisWorkbook.Sheets("Total")...

Good evening Unable to find any specifics on utilizing the Countif Function referencing a specific cell. This example I'd like to count the number of instances which are below the entered value in A1. Thanks all Dim lr2 As Long lr2 = Range("C" & Sheet1.Rows.Count).End(xlUp).Row...

Hi, Following code errors on highlighted line with Error 13, type mismatch: Sub Test1() Dim x As Long Dim arr() As Variant With Sheets("Sheet1") x = .Cells(.Rows.Count, 1).End(xlUp).Row arr = .Cells(25, 1).Resize(x - 24, 2).Value End With For...

If I run the code below it works fine. First test1 and then test2. As long as the width of the columns show the whole number in the cells!! But if I make column A as narrow as the number change to ## and hide column B, test2 will not find the first and the second number?! (I found a workaround...

Hey All I've got a perfectly fine and working Macro but I need to tweak it and can't work it out :-/ I need to be able to Copy and Paste a range of cells from one worksheet to the bottom of another in the same workbook, that's what this code does; LastCol = Cells(1...

This works: MaxVal = Application.WorksheetFunction.Max(Sheet1.Range("A1:F1")) but not this: MaxVal = Application.WorksheetFunction.Max(Range(Cells(1,1), Cells(1,6)))) neither do these: MaxVal = Application.WorksheetFunction.Max(Range(Sheet1.Cells(1,1), Sheet1.Cells(1,6)))) MaxVal...

Evening all Unsure why the following is resulting in an incorrect value. Cells(1,7).value = application.WorksheetFunction.CountA(Range("B:B")

here is all the code for my user form. the form initializes just fine and I can type values into all my text boxes. but when I click the OK Button (CommandButton1) to write the date to row 2 of my worksheet I get the following error. "Object doesn't support this property or method" for this...

I'm using the code below to insert a new column to the right of an existing column (based on existing column's name). The code then inserts an equation from the second row to the last row. The first half (with the vlookup) works fine. However the second half (with the int formula) causes the...

<code class="vb plain">' remove top rows down to row containing "Msn #" Dim rng As Range Dim X As Long Const strstart As String = "Msn #" X = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Cells(1, 1).Resize(X).Find(what:=strstart, LookIn:=xlValues, lookat:=xlWhole)...

This is my data: 1 4 2 5 3 6 What is wrong with this? Dim MyArray() As Long MyArray() = Cells(1, 1).CurrentRegion.Value Seems to only work if MyArray is declared as a Variant. Thanks

Hello everyone Basically there are word documents stored on a webpage that I would like to extract information from and put inside an excel table. I already have a code to loop through word documents of a given computer folder path to extract information from them and I want to be able to do...

Could someone explain why this coed to delete every other column does not work. Sub delete_every_other_column() lastcol = Cells(1, columns.Count).End(xlToLeft).Column For c = lastcol To 2 Step -2 Cells(1, c).EntireColumn.Delete Next c End Sub

I searched in the Internet and I found tons of stuff related to setting Header/Footer (Left, Right, Centre). However, I am trying to do the opposite and I have not succeeded. What I need is to get the text (ONLY THE TEXT) from CenterHeader. The text of CenterHeader was "Greece". I run the...

this code finds 2 non hypotenuse sides that form a right angle triangle then checks if it is a multiple of a previous "find". it finds nothing after 65 72 97 ie it does not find 65 156 169. J column is a list of prime numbers below 100 Sub Macro4() ' ' Macro4 Macro ' Macro recorded...

Hi all, The format of the dates that I'm using are different although I both formatted them in the same way. I copied them to a cell in my workbook and the date that is visible in the cell and the date that is visible in the inputbox above the workbook stay different. It's like super...

Hi, I want to look up a couple of values based on a If then code, for instance: If Cells(x, 1) = Cells(1,4).Value Then mycount= mycount + Cells(x,2).Value End if Cells(1,5).Value = mycount Then I want to see if there's a difference between the Values in E1 and F1 (Value of F1 is given), so...