Very well presented. This is the first of the tutorial that explained array in an uncomplicated way. Thanks very much. Can you make a Search Module tutorial using UserForm with multiple condition and output the result in a listbox. An example application system for tracking employee training where a company has more than 20 types of health & safety training. 1st search condition is employee lastname or employee ID, 2nd condition is training description and 3rd search is status of certification (expired: soon to expire:active). Hope you can provide some guidance. Thanks a lot.

Great Video! Trank you very much!

Its working for me too. Thanks for the video

Great video thank you so much ❤❤❤

Bravo good job

Many thanks. Excellent video, solves my problem

@LoveCodingAndPlay

3 жыл бұрын

Glad it helped

Wow, great video. Your Vba writing speed is amazing. I do similar but try using dictionary object to save row number of every fname lname combination. Cleaner and less writing

your video is amazing

@LoveCodingAndPlay

4 жыл бұрын

thank you

This is great.....I will like to share my worksheet with you(am new to VBA), am trying to Map columns headers on 2 different sheets(rawdata sheet and clean sheet) before coyping the columns rows. Will appreciate your feedback on this.

Doesnt really work as fast for mine tho, any improvement i can make? I have data that has equivalent to 7k rows and 30 columns. takes like a min to load..

nice but we cnt properly understand bcz it was big data of array if you cn same array programs with small data then you can help with us👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻

Hi Jay, Thanks for your video. Can you please do a video on RFM using VBA arrays. Thanks, Vijay

@LoveCodingAndPlay

3 жыл бұрын

What is RFM?

You are master and best of the best every seen👍👍👍👍👍. thank so much. i have subscribed your channel. what if you have a list of employees, date and status in sheet1. if employee status is "" then copy/move to and on the the employee whos status is will be taken his duty/place. Waiting your video soon.

@LoveCodingAndPlay

3 жыл бұрын

That should be easy even without using an Array, sure I'll plan a great video for that. Thanks for the supportsf

Hi Jay, for the SEARCH DATA sheet. When I start the column from "F" not "A", I did as below but it gave does not return anything. Could you please advise? Tks Sub array_Match_Data() Debug.Print Format(Now, "hh:mm:ss") Dim rSH As Worksheet Dim sSh As Worksheet Set rSH = ThisWorkbook.Sheets("RAW DATA") Set sSh = ThisWorkbook.Sheets("SEARCH DATA") Dim rawArray() As String Dim searchArray() As String ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 9) ReDim Preserve searchArray(1 To sSh.Range("F" & Rows.Count).End(xlUp).Row, 1 To 7) For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row For b = 1 To 9 rawArray(a, b) = rSH.Cells(a, b) Next b Next a For a = 1 To sSh.Range("F" & Rows.Count).End(xlUp).Row For b = 1 To 7 searchArray(a, b) = sSh.Cells(a, b) Next b Next a Dim fName As String For a = 1 To UBound(searchArray) fName = searchArray(a, 1) For b = 1 To UBound(rawArray) If rawArray(b, 1) = fName Then searchArray(a, 2) = rawArray(b, 7) searchArray(a, 3) = rawArray(b, 8) searchArray(a, 4) = rawArray(b, 9) Exit For End If Next b Next a 'Transfer data back For a = 2 To UBound(searchArray) For b = 2 To 7 sSh.Cells(a, b).Value = searchArray(a, b) Next b Next a Debug.Print Format(Now, "hh:mm:ss") Debug.Print "Process Completed" End Sub

@kamalakannan.1971

3 жыл бұрын

If the search sheet had data starting at column F you need to add 5 to the column index b in the two loops. See belo Sub array_Match_Data() Debug.Print Format(Now, "hh:mm:ss") Dim rSH As Worksheet Dim sSh As Worksheet Set rSH = ThisWorkbook.Sheets("RAW DATA") Set sSh = ThisWorkbook.Sheets("SEARCH DATA") Dim rawArray() As String Dim searchArray() As String ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 9) ReDim Preserve searchArray(1 To sSh.Range("F" & Rows.Count).End(xlUp).Row, 1 To 7) For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row For b = 1 To 9 rawArray(a, b) = rSH.Cells(a, b) Next b Next a For a = 1 To sSh.Range("F" & Rows.Count).End(xlUp).Row For b = 1 To 5 searchArray(a, b) = sSh.Cells(a, 5 + b) Next b Next a Dim fName, lName As String For a = 1 To UBound(searchArray) fName = searchArray(a, 1) lName = searchArray(a, 2) For b = 1 To UBound(rawArray) If rawArray(b, 1) = fName And rawArray(b, 2) = lName Then searchArray(a, 3) = rawArray(b, 7) searchArray(a, 4) = rawArray(b, 8) searchArray(a, 5) = rawArray(b, 9) Exit For End If Next b Next a 'Transfer data back For a = 1 To UBound(searchArray) For b = 3 To 5 sSh.Cells(a, 5 + b).Value = searchArray(a, b) Next b Next a Debug.Print Format(Now, "hh:mm:ss") Debug.Print "Process Completed" End Sub

i hope you can send the practice file so i can practice along

Could we use arrays in another workbook if our raw data in another workbook and search data in another workbook

@LoveCodingAndPlay

2 жыл бұрын

I think the excel query is much helpful for that scenario instead of manipulating data thru array

@Ram__o9

2 жыл бұрын

@@LoveCodingAndPlay thanks!!

This code doesn't work even before you put in the loop.