VBA Cells().value is not working but range().value is working

the problem appear because the cells().value is not using (") however if you using range("").value it is mandatory for (") in parenthesis.


example :

incorrectly : Cells("26, 2").Value = "b"
Correctly : Cells(26, 2).Value = "b"

try to delete the double quotes ("") if you using cells.value

however if you using range("").value should be there double quotes ("")

CREATE SEQUENCE ID IN EXCEL DATA COLUMNS

FOR EXAMPLE DATA EXCEL :

DATA :
A
B
C
A
A
B
D
C
C
C


So, we need to create the sequence number in the excel data using function,
if the data in Column "A" and the first row is 2 the function is :

=COUNTIFS($A$2:$A2, A2)


It will be creating the sequence id for all columns checking the all data.

it has worked well as proof picture on top.

FARIZ SETIAWAN

in Bahasa,
Ini merupakan cara untuk membuat sequence number didalam excel dengan menggunakan function,
id urut dalam excel dengan melakukan pengecekan terlebih dahulu pada seluruh data.
sehingga data yang dihasilkan valid.

VBA Text to Column solution with functionable

Text to column VBA to generate general type to text with function. following this script :

1. Create Function
2. Call the function
3. Function detail

---1. Create function----

Function TextToColumn(Reng As Range, Col As Integer, tipe As Variant)

Reng.TextToColumns Destination:=Reng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Col, tipe), _
TrailingMinusNumbers:=True

End Function

---2. Call the function----
Worksheets("Sheet1").Select
Call TextToColumn(ActiveWorkbook.Sheets("Sheet1").Range("DD:DD"), 108, xlTextFormat)

'Reng = ActiveWorkbook.Sheets("Sheet1").Range("DD:DD") So, the range is columns DD
'Index Columns = 108 as counting column A = 1 so Column DD is 108
'Tipe = xlTextFormat
'example for other type such as xlGeneralFormat or following this reference table :
'xlDMYFormat 4 DMY date format.
'xlDYMFormat 7 DYM date format.
'xlEMDFormat 10 EMD date format.
'xlGeneralFormat 1 General.
'xlMDYFormat 3 MDY date format.
'xlMYDFormat 6 MYD date format.
'xlSkipColumn 9 Column is not parsed.
'xlTextFormat 2 Text.
'xlYDMFormat 8 YDM date format.
'xlYMDFormat 5 YMD date format.
'if you want the type is date you can choose xlDMYFormat to change xlTextFormat

'it is work well for my worksheet.

Visual Studio error system.runtime.interopservices.sehexception: 'external component has thrown an exception.' / SEHException Class (System.Runtime.InteropServices)

Hi Everyone, Im Fariz i want to share how to fix problem error system.runtime.interopservices.sehexception: 'external component has thrown an exception.' or OleDbConnection gets "External component has thrown an exception." So, i have fixed this issue. the issue because you had installed microsoft access database engine (x64) and (x32) in your PC or Laptop so the program is confused. so, you need to open the control panel and uninstall microsoft access database engine one of them, i try to uninstall is bigger file and checklist automatically restart. after uninstall microsoft access database engine 2016 the program already work to connect database.

Fix an Error, An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in UPDATE statement.

I will be share how to fix this issue This morning, I got msg error : An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in UPDATE statement. ERROR QUERY : "UPDATE T_U SET PASSWORD ='" & TextBox3.Text & "' WHERE ID ='" & Home.ToolStripStatusLabel4.Text & "'" on VB.NET when Update to database access Office 365 such as picture below :

so, to fix the issue, I change the name of header with additional brackets [ ] FIX QUERY : "UPDATE T_U SET [PASSWORD] ='" & TextBox3.Text & "' WHERE ID ='" & [Home.ToolStripStatusLabel4.Text] & "'"

and it will be successful, in syaa Allah.

VBA Format date to text

There are 3 ways to doing these, 1. Format(activeworkbook("Sheet1").range("A1").Value, "dd/mm/yyyy hh:mm:ss AM/PM") 2. WorksheetFunction.Text(FormattingValue, "hh:mm:ss AM/PM") 3. "'" & Format(activeworkbook("Sheet1").range("A1").Value, "dd/mm/yyyy hh:mm:ss AM/PM") or you can try to change format each column, which is : --> Activeworkbook("Sheet1").range("A1:A10000").NumberFormat = "@" 1-3 for change directly by column and other option for change the column format before value adding. 5 September 2022 Kopipas _ Cibitung

VBA TEXT TO COLUMNS - FIXED WIDTH DATA TYPE

I want to make columns from text type to general type,

because when i tried to vlookup the data, the values were got #N/A.

Worksheet name = MP

Column target D1:D10000

its work code from text to general vba:

VBA TEXT TO GENERAL

ThisWorkbook.Sheets("MP").Activate

ThisWorkbook.Sheets("MP").Range("D1:D10000").Select

Selection.TextToColumns DataType:=xlGeneralFormat, _

    ConsecutiveDelimiter:=False, Space:=False

You can modify Data type likes below,

xlColumnDataType

XLCOLUMNDATATYPE

XlColumnDataType can be one of these XlColumnDataType constants.

xlGeneralFormat. General

xlTextFormat. Text xlMDYFormat. MDY Date xlDMYFormat. DMY Date xlYMDFormat. YMD Date xlMYDFormat. MYD Date xlDYMFormat. DYM Date xlYDMFormat. YDM Date xlEMDFormat. EMD Date xlSkipColumn. Skip Column Hope can help you.