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.

Masalah Visual Studio Service pack 1 saat install SQL Server Atau Menjalankan Miscrosoft Visual Studio 2010

 Masalah tidak dapat menginstall SQL server dan Visual Studio Service Pack 1

Seringkali terjadi saat menginstall sql server 2012 atau sql server 2014 yang gagal karena visual studio 2010 service pack 1.

Pada saat install sql server salah satu requirement  tidak terpenuhi. Error message

Prior Visual Studio 2010 instance requiring update

Jika anda Pendapatkan masalah sama seperti ini.

Saya akan berbagi pengalaman dan Alhamdulillah kini berhasil. Setelah 4 hari saya mencari cara untuk menyelesaikan masalah ini sampai tidur tidak nyenyak, mikirin skripsi tinggal seminggu lagi. Hahaha

Dalam kasus ini saya ingin menginstall sql server 2014

Namun saya sudah menginstall Visual studio 2010 dilaptop

Ingat baik-baik kondisinya saya sudah install vb 2010 dilaptop ketika ingin install sql server 2014 mengalami error seperti ini. Jadi perlu dibedakan nanti kasus anda dan yg saya alami.

Banyak yang menatakan ini masalah WCF RIA services. Jujur saja saya awan tidak mengerti maksudnya dan hal tersebut malah membuat saya pusing. Alhamdulillah saya menemukan cara yang lebih efektif.

Pertama service pack 1 yang terdapat di dalam file yang di extract oleh SQL SERVER 2014 yaitu

YourFolder_SQL_Extract\redist\VisualStudioShell\VS10SP1-KB983509.MSP

VS10SP1-KB983509.MSPà itu adalah visual studio service pack 1

Atau kamu bisa download disini

http://download.microsoft.com/download/2/3/0/230C4F4A-2D3C-4D3B-B991-2A9133904E35/VS10sp1-KB983509.exe

coba lihat video ini https://www.youtube.com/watch?v=18QM3gqYaGA.

Coba double click dan install. jika tidak berjalan sama seperti saya. Anda perlu melanjutkan step selanjutnya

If you failed to download vs10sp1– you must going to next step

1.       Kamu harus uninstall visual studio 2010

2.       Kamu masuk ke folder temp dan menghapus semuanya (cleaner)

C:\Users\yourPC\AppData\Local\Temp atau run lalu tulis %temp% kemudian hapus semuanya

3.       Setelah uninstall vb2010 dan cleaning selesai kamu install SQL SERVER (saya gunakan koneksi internet dan saya belum mencoba apabila tidak menggunakan internet)

4.       Suksess SQL server berhasil di install

5.       Install kembali Visual studio 2010

Coba buka Microsoft Visual studio 2010 yang sudah di install

Jika bermasalah pada instalasinya coba gunakan koneksi internet. Apabila anda mendapatkan masalah lain yang sama seperti saya. Seperti gambar dibawah saat ini

Kondisinya yaitu seteleh SQL Management studio berhasil dibuka Kini Visual studio tidak bisa dibuka dengan alasan yang sama yaitu service pack 1. Ini pesan errornya

Only some of the Microsoft visual studio  2010  product on this computer have been upgraded to service pack 1. None will work correctly until all have been upgraded.

To Upgrade, Close all  Visual Studio product  and then install Microsoft Visual Studio 2010 Service pack 1, Which you can download here. If setup starts in maintenance mode, Select Repair.

Microsoft Visual Studio will now Close.

Tenang bro. 

Bersyukurnya klo sudah sampe step ini Visual studio service pack 1 sudah bis di download.

Kamu hanya perlu install lagi

Stepnya

• 1.       Download VS10SP1-KB983509.MSP bagi kamu yang sudah punya langsung install saja.
• 2.       VS10SP1-KB983509.MSP
• 3.       Setelah sukses coba masuk ke Microsoft Visual studio
• 4.       Apabila masih gagal , coba kamu download VS10SP1-KB2736182.exe
• https://www.microsoft.com/en-us/download/confirmation.aspx?id=34677
• 5.       Lalu install
• 6.       In syaa Allah suksess dan bisa dibuka Microsoft Visual Studionya

Bagi pembaca tolong pahami kasusnya dan step by stepnya baik-baik.

Sumber reference :

https://stackoverflow.com/questions/40231855/vs-2010-not-working-and-installing-visual-studio-2010-sp1-update-in-windows-10