variant

I have the following code that basically searches column O and deletes any rows that contain the words in the array. Only problem is it wont remove rows where the words have capitals in or vice versa. I have lots of keywords to search for so adding different versions of each word isn't an...

I'm trying to insert value ex. Canada in a column to be defined by the user (prompt) and based on cell content, column also defined by the user. Here's my try: Dim FinalRow As Long Dim n As Long Dim ColumnOU As Variant Dim ClassifResult As Variant...

I have code that stores ranges like "D4" and "E4" in variables x and y respectively. However, I run into a bug with the .delete command... Any ideas on how I can fix this? I also want to have excel move that column up where the clear contents took place. Private Sub CommandButton1_Click()...

Yes, I believe ive done it. Anyone got any comments/queries please say. Function MOVEME27(a As Variant, b As Variant, Optional CELLR As Variant, Optional cellq As Variant) '21/05/2018 works copied to ar4 '' 03/06/2019 23:30 was cellr as range , cellq as range - changed to variants Dim...

How do I add a continuous range say 5 to 16? Instead of adding 5 and 16 as below? I tried "To" and ":" for the "," but didn't work out. Sub oSum() Dim n&, s As Double, TbRay As Variant TbRay = Array(5, 16) For n = 0 To UBound(TbRay) s = s + Val (UserForm1.Controls("TextBox" &...

Hello, I found the below code on a KutTools guide and modified it a bit to suit my needs: #If VBA7 And Win64 Then Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As LongPtr, ByVal lpOperation As String, _...

So, right now I have a functioning code, good or bad one, but what I want to know, is the following: How can split the code up in sections, so I have a better overview? Right now I have one long code, which makes hard to go through. How Can i manage the code, so that I begins as soon as i open...

I'm trying to pull a huge column of data into an array (500,000 rows), then remove some items that are direct duplicates of previous item, and then basically output it into a new column.... I have been going around in circles for hours now trying to do this and am not getting anywhere.. I...

Hello everyone :) I need your help with my code. I get runtime error #9 (subscript out of range). I have a function to check if a value is in an array or not. I can do this with other ways but I need to understand what is wrong with my writing. I hope you can help me answer that :) 'setting...

At one point, I had this working, but with all my futzing, I've jacked it up. Can anyone point out why nothing gets sorted?? The colors are the standard yellow, light green, and light blue from the default color palette. I'm at a complete loss; I tried using both .ListColumns and...

The following code snippet works. The optional parameter p3 can be "ON", "OFF", or omitted (missing). Public Function Temp(p1, p2, Optional p3 As Variant) As Variant . . . If Not IsMissing(p3) Then Select Case UCase(pErrMsgSw) Case "ON": ErrMsgSw = True Case "OFF": ErrMsgSw =...

In Sheet1 I have 10 columns of data. I want to extract only the second column, so have used this: Dim TotalArray() As Variant TotalArray() = Sheet1.Cells(1, 1).CurrentRegion.Value Dim NewArray() As Variant NewArray = Application.Index(TotalArray(), 0, 2)...

I tried using the function belows 1. Function to help convert range to array from an exisiting file. 2. Function to include workday1 with the exceptions of holidays (as per covert rangetoarray) The output i get when i use workday1 function is #VALUE . Any help will be greatly appreciated...

CROSSPOSTED FROM HERE. Hi everyone. I have a problem with my VBA code. I have certain functions SUM_DIST and SUM_DISTN that require a range of cells as input. Both are working fine and in order. SUM_DIST has to be ran only once, then SUM_DISTN has to be run thrice, with SUM_DISTN requiring...

Hi, Any ideas how can i get cell address values of EACH dates which are displayed in a range which are in Sheet1 Structure of sheet1 <tbody> A B C D E F G 1 abcd 01-01-2019 300 02-01-2019 400 03-01-2019 500 2 xyz 03-01-2019 200 03-01-2019 100...

Hi guys, I got the codes from online resources, when i run it to do k-means clustering, it seems don't work as it should be (it should be a 6 columns x 4 rows table, but it only returns 5 columns x 3 rows table). I have data in datasheet (worksheet) in 6 columns x 33 rows table. Then, i have...

I am trying to filter a table with an array, but the table is only being filtered by the last element. What am I doing wrong? dim varI as variant, arrT() as variant dim ws1 as worksheet Dim loTBL As ListObject Dim intSGL As Integer arrT = Array("101000", "109000", "131000", "131900") For...

Hi, Would someone be willing to eyeball my code below and advise why it is failing at the line: ".Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete"? What I find confusing is: 1. There is no sheet protection 2. The table headers are not selected 3. Changing the "Delete" method to...

Hello, I need help. I found on this forum an amazing macro. https://www.mrexcel.com/forum/excel-questions/873686-find-keyword-density-rows-columns.html Sub MG11Aug55Dim Rng As Range, Dn As Range, n As Long, sp As Variant, omax As Long, R As Variant Dim K As Variant, Str As String, Lg, NoStr As...

Hello, Im attempting to zip files based on a date range. I want a pop up to record the date range and zip accodingly. Here is the code i've written in vba for access. I would love your help. Thanks. Sub CreateZipFile(sPath As Variant, zipName As Variant) Dim ShellApp As Object Dim MyObj As...